In this Essential Guide to SQL Server 2014 series I’ve been covering some of the most important new features in SQL Server 2014. In the last column I covered some of the important AlwaysOn enhancements in SQL Server 2014. In this column I’m going to dive into one of the most important new performance features: Buffer Pool Extensions. Buffer Pool Extensions can be an important new feature for improving the performance of OLTP applications. Let’s take a deeper look at how SQL Server 2014’s Buffer Pool Extensions can help improve application performance.
First, let’s take a quick look at the Buffer Pool itself. The SQL Server Buffer Pool is an in-memory cache that holds query results. SQL Server uses its Buffer Pool to cache the results of queries which allows subsequent queries that share the same results to perform much faster as they can pull the data from the buffer pool rather than needing to go back to disk to retrieve the results. The in-memory data retrieval performance provided by the buffer pool is significantly faster than the I/O required by disk read operations. SQL Server’s Buffer Pool dynamically expands and contracts in response to workload. SQL Server’s Buffer Pool consists the buffer pool and the buffer manager. The buffer pool is a collection of 8K pages that contains data from recent query results. The buffer manager handles reading data or index pages from the database disk files into the buffer cache and writing modified pages back to disk.
While the Buffer Pool provides a great way to improve the performance of your queries the size of the buffer pool is limited by the total RAM available to the SQL Server instance. It can also be limited by the max server memory configuration option. If it’s not limited SQL Server will dynamically grow the Buffer Pool until the maximum available memory in the server is between 4 MB and 10 MB. When that limit is reached the Buffer Pool will cease to grow. Buffer Pool Extensions provide a way of expanding a server’s buffer pool capacity by taking advantage of flash storage like SSD drives that might be present in the system. The low latency and fast random I/O performance of today’s SSDs allow them to be used to extend the SQL Server Buffer Pool. This feature can be particularly useful in older servers where the maximum memory has been reached but the SQL Server instance would still benefit by having a larger Buffer Pool. Taking advantage of Buffer Pool Extensions enables SQL Server 2014 to accommodate a larger database working set – without requiring expensive server replacements.
Buffer Pool Extensions are available for the SQL Server 2014 Enterprise, Business Intelligence and Standard x64 editions. The size of the buffer pool extension can be up to 32 times the value of max_server_memory for the SQL Server Enterprise edition and up to 4 times the max_server_memory value for Business Intelligence and Standard editions. 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.
You can enable Buffer Pool Extensions using the ALTER SERVER CONFIGURATION command like you can see below:
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
SIZE = 50 GB
FILENAME = 'F:\SSDBUFFERPOOL.BPE'
You can find more information about SQL Server 201’s new Buffer Pool at MSDN Buffer Pool Extension and the SQL Server Blog.
