Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Buffer Pool Extension is a feature that was introduced in SQL Server 2014. With this feature, SQL Server uses the disk we specified for Buffer Pool. (To avoid a performance problem, the disk you specify must be fast. For example, SSD Disk)
So what is Buffer Pool?
In SQL Server, data and index pages are not read directly from the disk. When the user wants to access the data, the data is transferred to the buffer pool area and cached. The data in the buffer pool is then forwarded to the requesting user.
In this way, frequently accessed data is kept in the buffer pool. For detailed information, I recommend you read the article “What is Database Checkpoint“.
Buffer Pool Extension is an ideal choice for database servers with insufficient memory. If you have a SSD disk defined on this server, you can use this SSD disk as memory.
If a problem occurs with the disk we use for Buffer Pool Extension, SQL Server automatically disables the Buffer Pool Extension.
You can use Buffer Pool Extension only in Enterprise and Standard Edition.
You can use the following two dmw for Buffer Pool Extension.
1 2 | select * from sys.dm_os_buffer_pool_extension_configuration select * from sys.dm_os_buffer_descriptors |
To prevent data loss, only the committed data is written to the Buffer Pool Extension. We can activate Buffer Pool Extension with the following script.
1 2 3 | ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'C:\MSSQL\BufferPoolExtensionOrnek.BPE',SIZE = 10 GB) |
If the field you set for Buffer Pool Extension is smaller than the MAX SERVER MEMORY set for SQL Server, you will get an error as follows.
For MAX SERVER MEMORY and other settings, I recommend that you read articles “Numa Nodes, MAX/MIN Server Memory, Log Pages In Memory and MAXDOP” and “sp_configure (Server-Level Configurations in SQL Server)“.
Msg 868, Level 16, State 1, Line 1
Buffer pool extension size must be larger than the current memory allocation threshold 10240 MB. Buffer pool extension is not enabled.
You can close Buffer Pool Extension as follows.
1 2 | ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF |
If you want to open the Buffer Pool Extension again after turning it off, if the value you set is larger than MAX SERVER MEMORY and is smaller than the field you previously set for Buffer Pool Extension, you will still get this error.
To get rid of this situation, you will need to restart the SQL Server Service.
Msg 868, Level 16, State 1, Line 1
Buffer pool extension size must be larger than the current memory allocation threshold 10240 MB. Buffer pool extension is not enabled.
