How to set up the SQL Server buffer pool extension feature

Enabling the buffer pool extension

To get started, find the currently configured value of the MAX SERVER MEMORY parameter for your instance of SQL Server using the SP_CONFIGURE stored procedure as shown below.

USE master
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'max server memory (MB)'
GO

In this example, let's say your instance is configured to use 16 GB of available RAM. In enabling and configuring the buffer pool extension feature, you set it to use 32 GB as the new maximum file size and add a dedicated 150 GB SSD device as the P: drive on the SQL Server system.

Before you go ahead and enable the extension, you also need to create a folder named "BUFFER POOL EXTENSION" on the P: drive. Without this, your next query will fail. After setting up the folder, execute the script below.

USE master
GO

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
  (FILENAME = 'P:\BUFFER POOL EXTENSION\SQLServerCache.BUFFER POOL EXTENSION', SIZE = 32 GB);
GO

Disabling and altering the extension

To disable the buffer pool extension feature on SQL Server 2014, execute the script below. However, to avoid having a negative impact, restart the SQL Server instance after disabling the extension.

USE master
GO

ALTER SERVER CONFIGURATION
  SET BUFFER POOL EXTENSION OFF;
GO

Before you alter existing buffer pool extension parameters, disable the feature and then re-enable it with the modified parameters. To alter or modify the size of a buffer pool extension file -- for example, to increase the file size to 64 GB -- execute the script below.

USE master
GO

ALTER SERVER CONFIGURATION
  SET BUFFER POOL EXTENSION OFF;
GO

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
  (FILENAME = 'P:\BUFFER POOL EXTENSION\SQLServerCache.BUFFER POOL EXTENSION', SIZE = 64 GB);
GO

When you re-enable the extension, the memory will be reused without having to restart the SQL Server instance.

Buffer pool extension best practices

Microsoft says that the buffer pool extension can be up to 32 times the value of the physical memory parameter (i.e., MAX SERVER MEMORY) in the case of a SQL Server Enterprise Edition instance, and up to four times with a Standard Edition one. But it recommends a ratio of 1:16 or less between the amount of memory and the size of the extension and cautions that a ratio of 1:4 to 1:8 could be optimal as a starting point.

According to Microsoft, users should also fully test the extension in a quality assurance or preproduction environment to identify the right file size before putting it into production.

In addition, SQL Server system performance can be negatively affected if the overall buffer pool size is reduced because the extension feature is either disabled or the file size is decreased. If you do disable the buffer pool extension, make sure you quickly restart the SQL Server instance -- the system won't reclaim the memory used to support the extension until you do.