Demystifying Buffer Pool Extensions with SQL Server 2014

With every release of SQL Server, the Microsoft product team introduces new features that can improve the performance of the applications using SQL Server as the backend. The buffer pool extension is one such feature introduced in SQL Server 2014. It can help DBAs to increase the size of SQL Server memory area (a.k.a. buffer pool) and extend it to a physical file located on disk subsystem. This feature is especially useful in scenarios in which all the memory slots of the physical server have been occupied and you still need to add more buffer pool region.

When we enable this feature, SQL Server divides the typical buffer pool into two areas. If you are a hardware expert, it will be easy for you to understand them as L1 and L2 cache. The L1 cache (level 1 cache) is in the RAM that can hold clean and dirty pages. The L2 cache will be on disk, and will have only clean pages. This L2 cache is buffer pool extension file and is located on disk but is still part of buffer pool region of SQL Server memory.

Configuring Buffer Pool Extensions

The steps to configure this feature are pretty straightforward. Specify the path and the size–and that’s it. Keep in mind that the size of the buffer pool extension file has to be more than the currently located memory.

For example, let’s assume that we have 50 GB of max server memory configured. In this case, the minimum size of buffer pool extension file must be more than 50 GB. If we try to create a file less than the current allocation, we will get this error message:

Msg 868, Level 16, State 1, Line 40
Buffer pool extension size must be larger than the current memory allocation threshold 50000 MB. Buffer pool extension is not enabled.

The maximum size of the buffer pool extension file is also limited based on the edition of SQL Server:

• Standard edition: the buffer pool extension file size can be up to 4 times current memory allocation
• Enterprise edition: the buffer pool extension file size can be up to 32 times current memory allocation

Because of these limitations, we have to bring down the value of max server memory on our demo SQL Server instance. You can also copy paste and follow along with us.

Note: Please don’t do this on production servers directly without testing first!

EXEC sys.sp_configure N'show advanced options', N'1'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'max server memory (MB)', N'1024'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'show advanced options', N'0'

GO

RECONFIGURE WITH OVERRIDE

GO

 

One the memory is limited, we can now increase buffer pool to extend to physical file.

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION

ON (FILENAME = 'E:\BufferPoolExtension\BPE.bpe' ,SIZE = 2 GB)

 

Learning About the Internal Workings of the Buffer Pool Extension

Here are the dynamic management views (DMVs) that can help in knowing about the internal working of this feature:

• sys.dm_os_buffer_pool_extension_configuration

• sys.dm_os_buffer_descriptors

Using this DMV we can find the pages which are in Buffer Pool Extension file:

Select *

from  sys.dm_os_buffer_descriptors

where is_in_bpool_extension = 1

 

 

Here are a few of the new extended events that have been added in SQL Server to capture the data about this feature:

• buffer_pool_extension_pages_written
• buffer_pool_extension_pages_read
• buffer_pool_extension_pages_evicted
• buffer_pool_eviction_thresholds_recalculated

There are also new performance counters related to this feature, added under the Buffer Manager object:

• Extension allocated pages
• Extension free pages
• Extension in use as percentage
• Extension outstanding IO counter
• Extension page evictions/sec
• Extension page reads/sec
• Extension page unreferenced time
• Extension pages writes/sec

Choosing the Best Drive for Buffer Pool Extensions

It would be very useful if the buffer pool extension file can be placed on Solid State Drives (SSD) rather than spinning media drives. If using physical drives, then will take same time  to fetch a clean page (from buffer pool extension file) as it would have been done from the data file. If the I/O speed of buffer pool extension file is the same as the MDF or NDF file, we may not notice very significant performance improvement.

Critical Considerations for Use of Buffer Pool Extensions

Finally,  a few critical considerations to keep in mind:

1. Planning is more important for this feature because we can’t reduce the file size once its current size.
2. While this feature can be very useful, it is not a substitute of adding more RAM.
3. This feature is very useful for OLTP workloads.
4. You will get the most benefit from this feature when the extension file is on faster media.