How many of you have been looking into ways of cost effectively improving the performance of your OLTP workloads, without having to buy new more expensive hardware? Most of you know already that memory is one of the key assets available for the SQL Server and especially buffer pool memory, which plays key role in storing data pages and in turn reducing the I/O demand. Have many of you seen your servers struggling from the workloads intensity increase that generated memory pressure and in turn exceeded planned capacity?

Did you see the machine level limitations that makes it hard to upgrade memory in those machines and you have been thinking that it would be great if you only could upgrade your memory in the same easy way you can upgrade your storage options?

You are not alone. We have heard similar feedback from many customers, so we tried to address it in the way that would allow you to avoid significant changes in your hardware or create cost-effective mid-size hardware configurations for OLTP workloads from the scratch.

The answer we come up with is Buffer Pool Extension feature (BPE) that targets nonvolatile storage devices, in particular SSD drives, as an extension for SQL Server buffer pool. Those devices have some advantages over direct memory increase: the first one, already mentioned above, is the higher flexibility of storage options over memory options; the second, might be even more important, is the greater price efficiency of available storage vs. memory.

What does Buffer Pool Extension offer?

A few things:

  • Performance gains on OLTP workloads, mostly on the read-heavy OLTP.
  • No risk of data loss. BPE only deals with clean pages.
  • No application modification required. Just enable the feature and you’re ready to go.
  • Simple feature syntax

Let’s start from the last bullet and take a look on the syntax. Assuming you already have SSD drives ready and configured in Windows the only syntax you need to be aware of is

ALTER SERVER CONFIGURATION 
 SET BUFFER POOL EXTENSION 
 { ON ( FILENAME = 'os_file_path_and_name' , SIZE = <size> [ KB | MB | GB ] ) 
 | OFF }

Note: ALTER SERVER CONFIGURATION command requires ALTER SERVER STATE privilege from the user executing the command.

Yes, this is it. This is how it translates into an actual example:

ALTER SERVER CONFIGURATION
 SET BUFFER POOL EXTENSION ON
 SIZE = 50 GB
 FILENAME = 'F:\SSDCACHE\EXAMPLE.BPE'

Looking at the example and going up by the list you can see that there is no additional database or application level syntax available for the feature. Just keep running your workloads as they are currently.

What does it mean that there is no risk of data loss? BPE only processes clean pages, which ensures that all data in the extension is already committed. Machine crash or power failure? No additional risk compared to the data in memory. What about SSD storage failure? The feature disables itself automatically and can be either re-enabled manually in the same session or attempts to re-enable automatically on instance restart.

Performance gain is always the trickier subject, mostly because in the real world no two workloads are alike and no two configurations are the same. However, there are some basic sweet spot recommendations you can start with:

  • Up to 32GB RAM available for SQL Server
  • Extension file sized 4x-10x of the memory size available to SQL Server
  • High throughput SSD storage on the machine. We also suggest using high endurance storage, despite the endurance being rarely directly related to performance

However, we all know that a silver bullet doesn’t always work in the real world, so there are some situations when your expectations should be limited:

  • Data warehouse workloads.
  • Write-heavy OLTP workloads.
  • Machines with more than 64GB of memory available to SQL Server

Now let’s take a look on the little more complicated scenario that would also allow us to discuss one more interesting point. Let’s assume you have BPE configured, but you don’t like its configuration. The only option available to you is to turn it OFF and then back ON.

ALTER SERVER CONFIGURATION
 SET BUFFER POOL EXTENSION OFF
 GO
 EXEC sp_configure 'max server memory (MB)', 12000
 GO
 RECONFIGURE
 GO
 ALTER SERVER CONFIGURATION
 SET BUFFER POOL EXTENSION ON
 SIZE = 40 GB
 FILENAME = 'F:\SSDCACHE\EXAMPLE.BPE'

This is the thing to keep in mind – when you turn OFF the BPE feature it automatically and most likely significantly reduces the amount of addressable memory in your instance’s buffer pool. This will immediately cause increased memory pressure, then increased I/O pressure, and then, in turn, performance drop. Make sure you keep this in mind when updating BPE configuration.

Now you are ready to start using the feature. All that you need is the SQL Server Enterprise on x64 architecture.

I hope you will like the feature and let us know what you think. You can download SQL Server CTP1 here. 

Evgeny Krivosheev,
SQL Server Program Manager