Buffer Pool Extension

A new feature which can help you improving the performance of SQL Server
By Vlad Roman Posted 26 September 2016

Buffer Pool Extension

Buffer Pool Extension

Introduced in SQL Server 2014 this feature increases the size of the SQL Server Buffer Pool by allowing the Buffer Pool to be extended above the amount of physical memory allocated to SQL Server. When you have reached the maximum physical memory, the Buffer Pool extension allows you to use solid state drives to increase the size of the Buffer Pool and get the performance gains that solid state drives offer over traditional disks.

So what is the SQL Server Buffer Pool anyway?

The Buffer Pool is the place in system memory that holds data and index pages read from disk. SQL Server uses its Buffer Pool to cache pages which allows queries that share the same pages to perform faster as they can pull the data from the Buffer Pool rather than needing to go to the physical disk to retrieve the data.

When performing a simple SELECT statement, SQL Server reads the data from disk into memory. At this point, the data is clean. Once the data is changed by performing an INSERT / UPDATE statement, it is marked as dirty. Eventually when a CHECKPOINT is performed the dirty pages are written to disk and marked as clean.

Clean pages may be flushed from memory when the Buffer Pool comes under pressure. At this point the data is gone from memory and SQL Server has to read it from disk the next time a user runs a query that requires the data.

Buffer Pool size is determined by server memory and the target server memory specified in the 'Max Server Memory' parameter. When that threshold is reached and SQL Server needs to read more pages, previous cached pages are discarded.

So in a nutshell SQL Server Buffer Pool Extension uses a Flash or DRAM memory device like an SSD disk as a cache to the Buffer Pool by creating a file on disk.

How to configure buffer pool extension?

Well you can enable Buffer Pool Extension on your server by using the script below:

USE master 
GO
ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON
    (FILENAME = 'C:\Databases\Test.BPE', SIZE = 2 GB)
GO

Be careful because in order to change the size of the Buffer Pool Extension you need to disable and then re-enable this option with its new size. The Buffer Pool can be disabled with the following script:

USE master
GO
ALTER SERVER CONFIGURATION 
    SET BUFFER POOL EXTENSION OFF
GO

Some recommendations that might be useful and taken into consideration:

  • According to Books Online the Buffer Pool Extension can have a size up to 32 times the size of RAM, but they recommend adjusting its value in concordance with the Max Server Memory parameter. Microsoft recommends a ratio between the size of the physical memory (max_server_memory ) and the size of the Buffer Pool extension of 1:16 or less. A good starting point is 1:4.
  • Use the fastest disk as possible for storing the BPE file.
  • Enabling Buffer Pool Extensions on a machine with a high amount number of writes may result in no performance improvements.
  • The RAM memory is always faster
Vlad Roman
Vlad Roman
DBA/DBD