Wednesday, June 3, 2015

Buffer Pool Extensions

One cool new feature in SQL Server 2014 is Buffer Pool Extensions, which allow you to use your SSDs like an extension of your system RAM, albeit a tad slower. It's no secret that adding memory to a SQL Server can improve your system's performance. Thankfully, memory prices fall logarithmically decade-over-decade and you can now get RAM for about ten bucks a gigabyte. Not bad! However, what if you've already maxed out your RAM and you still want more? BPE to the rescue!

How Buffer Pool Extensions Work

Buffer pool extensions work by using (fast) non-volatile storage devices, such as Solid State Disks, as an extension of your system RAM. Of course even the fastest SSD is not as fast as the slowest RAM available today, but SQL Server manages BPEs on SSDs in such a way that it's all gain and very little pain. With an available SSD, you can set up a BPE on that device with just a few T-SQL statements.

Where Does BPE Help?

You will likely see good performance gains on OLTP workloads -- particularly if they are of the read-mostly type. You don't have to modify your applications.  The feature is transparent to users.  There's no risk of data loss, since BPE only works with clean pages (already committed to durable storage).  Oh, and if the SSD fails, BPE automatically disables itself, though it can be re-enabled manually should you think it's just a hiccup.

Let's Set Up BPE!

Actually, there's very little to do, just:
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON ( FILENAME = 'path to file' , SIZE = KB | MB | GB )
On my laptop even though I don't have an SSD, I can do this:

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON ( FILENAME = 'C:\temp\cache.bpe' , SIZE = 10 GB )
I wouldn't recommend putting a BPE on your server's "C:" drive, but this illustrates just how easy it is!

Hey BPE, How ya doin?

No feature would be complete with out DMVs to monitor it.  The following are available:
  • sys.dm_os_buffer_pool_extension_configuration - Returns configuration information.
  • sys.dm_os_buffer_descriptorsReturns information about all the data pages that are currently in the SQL Server buffer pool. 

Where not to use BPE

Certainly there are some workloads where BPE is not a good fit,  These include:
  • Data warehouse workloads
  • Write-heavy OLTP
  • Machines with less than 64 GB RAM available to SQL Server

Get thee to an SSD!

Seriously, this is so easy to use, let's use it!

Futher Reading


No comments:

Post a Comment