SQL Server 2014 Buffer Pool Extension
Buffer Pool Extension is one of new features of SQL Server 2014 to increase SQL Server database performance by increasing amount of cache that SQL Server can use. Buffer pool extension enables database administrators to integrate random access memory like SSD disks or flash disks to SQL Server database engine buffer pool without any change on server, server configuration or database applications. Increased amount of database buffer pool improves I/O throughput by providing a more area for use of SQL Server database cache.
Please note that Buffer Pool Extension feature in SQL Server 2014 is only available on 64-bit Enterprise Edition and 64-bit Standard Edition of SQL Server 2014.
Attach flash disk or SSD (Solid State Disk) to SQL Server machine. Make sure your disk is NTFS file formatted instead of FAT32 file format otherwise you will not be able to define buffer pool extention with size over 4 GB.
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON (
FILENAME = 'E:\SQLServer2014.BPE',
SIZE = 5 GB
)
Since for demonstration I used FAT32 flash disk and set file size for SQL Server 2014 buffer pool extension to 5 GB which is over limit, SQL Server will throw following error message.
Msg 5123, Level 16, State 3, Line 3
CREATE FILE encountered operating system error Change file size failed while attempting to open or create the physical file 'E:\SQLServer2014.BPE'.
And please keep the official SQL syntax for SQL Server buffer pool extension configuration command. Please refer to T-SQL syntax
In some SQL Server tutorials, the SQL syntax used to enable buffer pool extension differs than the RTM release of SQL Server 2014. So keep an eye on the T-SQL syntax at SQL Server Books OnLine documentation.
For example, use brackets and use filename before you define file size. Otherwise following uses will cause SQL Server engine errors.
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON (
SIZE = 3 GB,
FILENAME = 'E:\SQLServer2014.BPE'
)
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '3'.
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
FILENAME = 'E:\SQLServer2014.BPE'
SIZE = 3 GB
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'FILENAME'.
I've formatted my flash disk using NTFS and ready to define a bigger memory area for SQL Server 2014 buffer pool extension configuration.
Run following SQL Server configuration command to enable buffer pool extension on target disk with specified file name and size.
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON (
FILENAME = 'E:\SQLServer2014.BPE',
SIZE = 5 GB
)
Please note that size of the cache can be defined as KB, MB or GB and the minimum size can be configured as the size of Max Server Memory. On the other hand the maximum size is limited to 32 times of the size of SQL Server Max Server Memory.
If you define the cache size less than the configured Max Server Memory for the target SQL Server 2014 instance, the SQL engine will not allow buffer pool extension creation with below error message.
Msg 868, Level 16, State 1, Line 2
Buffer pool extension size must be larger than the current memory allocation threshold 5120 MB. Buffer pool extension is not enabled.
After you turn on Buffer Pool Extension, you will be able to see the bpe file on the SSD disk (or on flash disk) as seen below
SQL Server database administrators can check buffer pool extension configuration on a SQL Server 2014 instance by querying sys.dm_os_buffer_pool_extension_configuration system view.
select * from sys.dm_os_buffer_pool_extension_configuration
The current size of the buffer pool extension is listed as KB, SQL developers or a DBA can display buffer pool file size in MB or GB using the following query on sys.dm_os_buffer_pool_extension_configuration dynamic management view (DMV).
select *,
current_size_in_kb / 1024 as MB,
current_size_in_kb / ( 1024 * 1024 ) as GB
from sys.dm_os_buffer_pool_extension_configuration
To manage buffer pool extensions, an other dynamic management view is sys.dm_os_buffer_descriptors. After database administrator has defined a new buffer pool extension and enabled on the SQL Server 2014 instance, you can query sys.dm_os_buffer_descriptors DMV to see what is placed in the buffer pool extension file. sys.dm_os_buffer_descriptors view has a column named is_in_bpool_extension
select * from sys.dm_os_buffer_descriptors where is_in_bpool_extension = 1
To disable Buffer Pool Extension, following Server Configuration alter command is used.
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF
If you set the buffer pool extension to OFF which means disable the SQL Server buffer pool extension usage, first the state of the related buffer pool is set to 1 which means disable is in progress.
state state_description
1 BUFFER POOL EXTENSION CLEAN PAGE CACHING DISABLE IN PROGRESS
And when the disable process is completed successfully, the state will be 0 which means "BUFFER POOL EXTENSION DISABLED"
state state_description
0 BUFFER POOL EXTENSION DISABLED
When the disable is in progress, database administrators can not enable another buffer pool extension on the server.
Msg 863, Level 16, State 1, Line 2
Attempt to enable buffer pool extension when in state BUFFER POOL EXTENSION CLEAN PAGE CACHING DISABLE IN PROGRESS is not allowed.
Recently I had experienced a problem while disabling a buffer pool extension. The turn off statement was cancelled during execution, then in system DMV although I could see the buffer pool extension file I could delete it manually but can not enable a new one or can not delete the existing one from SQL. What I get is as follows.
Msg 845, Level 17, State 1, Line 20
Time-out occurred while waiting for buffer latch type 4 for page (1:14), database ID 9.
I was able to stop and start the SQL Server service so I quickly restart SQL Server service. When the server is up, I see that the bpe file (buffer pool extension) file is recreated on the removable disk again and the state is seen as enabled. After I execute Alter Server Configuration command for disabling the buffer pool again, I could successfully remove the cache file from disk and from SQL Server configuration.
For more on SQL Server 2014 Buffer Pool Extension please refer to MSDN article.