Extending the Buffer Pool in SQL Server 2014 Increases Performance
Andy McDermid | | December 18, 2013
One means of increasing your database’s performance is integrating high-speed solid-state drives with the data buffer pool extension found in Microsoft SQL Server 2014.
The extension to the Database Engine buffer pool significantly improves I/O throughput, and ultimately, the system’s performance.
As Microsoft explains:
As data and index pages are read, and modified pages are read back to the disk, some of the active pages in the buffer cache can be written to disk and read back into the cache. These are random read-and-write operations consisting of between 4 and 14 KB of data. Although small, they compete for resources, and reduce latency and throughput.
The conventional approach to resolving these issues is to add more DRAM to the system. But DRAM is expensive. Spindles can be added –again at high cost.
Biz Nigatu, writing on the Database and Business Intelligence blog, notes:
The net effect is that this improves the I/O throughput.
The benefits of the buffer pool extension feature, according to Microsoft, are:
- Increased random I/O throughput
- Reduced I/O latency
- Increased transactional throughput
- Improved read performance with a larger hybrid buffer pool
- A caching architecture that can take advantage of present and future low-cost memory drives
When working with the buffer pool extension, Microsoft suggests using 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 for the extension. It suggests trying a ratio ranging from 1:4 to 1:8 for optimal performance.
Another suggestion is to thoroughly test the buffer pool extension before adding it to a production environment. Then, once it is being used, don’t make configuration changes or turn off the extension; either may cause performance problems.
Microsoft does note that some of the information it has provided on buffer pool extension is subject to change upon the final release of SQL Server 2014. More information is available online.
Subscribe to Our Blog
Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.
Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.
Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.