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
- 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
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:
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.
ALTER SERVER CONFIGURATION (Transact-SQL).
Buffer Pool Extension.