Select Page

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:

“The primary purpose of a SQL Server database is to store and retrieve data, so intensive disk I/O is a core characteristic of the Database Engine. Because disk I/O operations can consume many resources and take a relatively long time to finish, SQL Server focuses on making I/O highly efficient. The buffer pool serves as a primary memory allocation source of SQL Server. Buffer management is a key component in achieving this efficiency.”

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:

“[B]uffer pool extension is a server configuration that could potentially help us resolve I/O bottlenecks without the usual method of adding more DRAM. … The buffer pool extension feature extends the buffer pool cache by accommodating a larger database working set, which forces the paging of I/Os between RAM and the SSDs. This effectively offloads small random I/Os from mechanical disks to SSDs.”

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

IC655069

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS