Buffer Pool Extension.

Applies to: Microsoft SQL Server 2014.

Buffer Pool Extension (BPE) is a new feature introduced by SQL Server 2014 that can potentially increase performance of OLTP applications by allowing extension of SQL Server buffer pool to a file located on a non-volatile disk (that could be a Solid State Drive) with the purpose of storing clean pages only (dirty pages cannot be stored on the buffer extension). The buffer pool extension should be seen as a L2 cache where the standard buffer pool should be seen as the L1 cache.
 

Limitations, considerations, recommendations.

- Recommended for read-intensive OLTP scenarios. Not recommended on OLAP.
- It's a feature available for 64-bit SQL Server only.
- It's a feature available for SQL Server 2014 Standard, Business Intelligence and Enterprise only.
- MSDN library says that the minimum size specified for BPE should be the value of is the size of Max Server Memory, but based on my tests if you don't set the size of BPE to a size greater than the Max Server Memory option, you may get error message (Msg 868, level 16).
- The maximum limit is 32 times the size of Max Server Memory or RAM. For optimal performance set BPE to a size 4 to 8 times the size of Max Server Memory and avoid any changes to the size of the BPE.
- If BPE has been enabled and you want to modify the size of the file used by BPE on the non-volatile disk, then you need to disable it first and set the new size when you enable BPE again. If the size is less than previously set, SQL Server must be restarted for the changes to take effect on the non-volatile disk.
- The ALTER SERVER STATE privilege is required to enable BPE.


Related Dynamic Management Views.

Dynamic Management Views that are associated with BPE on SQL Server 2014 associated:

sys.dm_os_buffer_descriptors (a new column introduced is_in_bpool_extension).
sys.dm_os_buffer_pool_extension_configuration (a new DMV).







Related SQL Server Extended Events.

The following SQL Server Extended Events have been introduced in SQL Server 2012 related with the new BPE feature:

sqlserver.buffer_pool_extension_pages_evicted
sqlserver.buffer_pool_extension_pages_read
sqlserver.buffer_pool_extension_pages_written
sqlserver.buffer_pool_eviction_thresholds_recalculated

How to configure the instance for buffer pool extension.

The following example shows how to enable the buffer pool extension option and specifies a file name and size.






References.

ALTER SERVER CONFIGURATION (Transact-SQL).

Buffer Pool Extension.

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.