For an instructor lead, in-depth look at learning SQL click below.
The Buffer Pool Extension feature was introduced in SQL Server 2014 to extend the buffer pool cache with non-volatile storage like SSD (Solid-State Drive). In SQL Server buffer pool, the pages are managed on a LRU (Least Recently Used) basis and are classified into two types; clean pages and dirty pages. It provides the database engine with the option of utilizing SSDs as additional memory at a lower cost.
Enabling Buffer Pool Extension
The Buffer Pool Extension is disabled by default in SQL Server. So, how do we enable it?
|
1 2 3 4 5 6 |
-- Syntax to enable Buffer Pool Extension ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'SSDDrive:\FolderPath\BPE.bpe', SIZE = 50 GB) |
The above query enables Buffer Pool Extension and sets the location and size of the .bpe file. Note that maximum size can be up to 32 times the value of max server memory, but should not exceed 256 GB.
Disabling Buffer Pool Extension
Disabling the Buffer Pool Extension is as easy as enabling it. The syntax is as follows:
|
1 2 3 4 5 |
-- Syntax to disable Buffer Pool Extension ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF |
Measuring the Buffer Pool Extension Usage
You can measure Buffer Pool Extension usage via the performance counters added in SQL Server 2014.
|
1 2 3 4 5 6 7 8 9 |
-- Query to measure BPE usage SELECT cntr_value AS 'Page Life Expectancy' FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Manager%' AND counter_name = 'Page life expectancy' |
This code will return the Page life expectancy of the pages in the Buffer Pool Extension.
Conclusion
The Buffer Pool Extension in SQL Server provides an optimized way of outlasting cache to get the maximum benefit from fast storage devices like SSDs. It significantly improves I/O throughput and reduces latency, which leads to improved performance.