Manage Learn to apply best practices and optimize your operations.

How to set up the SQL Server buffer pool extension feature

SQL Server 2014 includes the buffer pool extension feature, which can speed up query performance. Ashish Mehta explains the benefits of the feature and how to enable and disable it.

The SQL Server buffer pool is a cache in SQL Server's system memory that holds query results. This allows subsequent...

queries that share the same results to perform faster, since SQL Server can pull the data from the buffer pool cache instead of having to get it from disk storage. SQL Server 2014 introduced the ability to extend the buffer pool to a solid-state drive or other nonvolatile storage device. The buffer pool extension feature, which will also be supported in SQL Server 2016, can help boost the performance of an online transaction processing system in a cost-effective way.

The most common approach to resolving the disk I/O bottlenecks that often slow down OLTP performance is to add more memory or high-performance disk drives to a SQL Server system. However, both of those options are expensive. That's where the SQL Server buffer pool extension becomes useful for organizations to implement. It enables the buffer pool in SQL Server, which consists of 8 KB data and index pages, to handle larger data sets at a relatively low cost. According to Microsoft, offloading I/O operations from conventional disks to lower-latency SSDs via the buffer pool extension should "significantly" improve I/O throughput.

The feature is currently available in the Enterprise, Business Intelligence and Standard x64 editions of SQL Server 2014. Enabling the buffer pool extension feature on SQL Server 2014 also provides a couple of advantages to database administrators. By using it, DBAs can boost query performance while limiting the amount of memory on a SQL Server instance through the SP_CONFIGURE MAX SERVER MEMORY parameter in Transact-SQL. They can also enable the extension during heavy processing workloads without restarting a SQL Server instance.

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.

Next Steps

Consider buffer pool size when consolidating SQL Server databases

Pay attention to the buffer pool when you virtualize your SQL Server instance

Use built-in tools for monitoring and troubleshooting SQL Server memory

This was last published in March 2016

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

PRO+

Content

Find more PRO+ content and other member only offers, here.

Join the conversation

2 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What is the biggest advantage you've found to using the SQL Server 2014 buffer pool extension feature?
Cancel
Might have to give this a try. I do have some that take quit a while to run. This just might be a cost-effective fix.
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close