Wanted: more RAM

In an ideal world (well, a DBA’s ideal world, anyway) all database servers would have enough memory to hold every last byte of data we would need to store. We wouldn’t worry about disk latency, and PAGEIOLATCH waits would be nothing more than a distant memory. But you and I, we live in the real world, don’t we? A world of budgets. A world of sacrifice. A world where we need to make do with the servers we can afford (or possibly the servers we could afford 2 years ago).

In the past, once a server’s memory was maxed out, there wasn’t a whole lot you could do. You tuned your code to reduce overall IO, you made sure your disk layout was as optimal as possible, maybe you implemented something like partitioning. But all of this was really just a way to compensate for a lack of memory. Only so much data was going to be able to fit into that finite buffer pool.

But what if you would expand that buffer pool without adding expensive memory? SQL Server 2014′s new Buffer Pool Extension feature allows you to do just that by extending your buffer pool onto non-volatile storage. This non-volatile storage will usually take the form of fast SSD, but technically it could be any disk.

How does it work?

When the buffer pool extention is enabled, SQL Server divides the buffer pool into 2 tiers. The level 1 (L1) tier is in RAM and can hold clean or dirty pages. The level 2 (L2) tier is on disk, and will only hold clean pages. The movement of pages between L1 and L2 is managed entirely by the buffer manager.

To enable the buffer pool extension, we use the ALTER SERVER CONFIGURATION command, but first, let’s check the default configuration by querying the DMV sys.dm_os_buffer_pool_extension_configuration.

 select * from sys.dm_os_buffer_pool_extension_configuration 

Now, let’s extend the buffer pool by 10GB.

 ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'C:\SSDDISK\BufferPoolExt.BPE', SIZE = 10GB);
GO 

If we query sys.dm_os_buffer_pool_extension_configuration again, we’ll see new information on our buffer pool extension.

And if we look at our SSDDISK directory, we’ll see the new file. This file will be deleted on instance shutdown and reinitialized at startup.

To disable the buffer pool extension, we use ALTER SERVER CONFIGURATION again:

 ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF 

So what happens if we want to make that extension smaller? Let’s say I messed up and I should have made the extension 5GB instead of 10GB. It’s currently disabled, so can I just enable it again at 5GB?

 ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'C:\SSDDISK\BufferPoolExt.BPE', SIZE = 5GB);
GO 

Nope. If I do that, I get this error:

Msg 868, Level 16, State 1, Line 5
 Buffer pool extension size must be larger than the current memory allocation threshold 10240 MB. Buffer pool extension is not enabled.

If I want to reconfigure the extension to a smaller size, I need to disable it, restart the instance, and then re-enable it at the new size. Which is a bit confusing given the line in BOL “When the buffer pool extension is disabled, all related configuration settings are removed from the registry.”  Just FYI.

Further reading

For more information on the buffer pool extension, please see

• Buffer Pool Extension – http://msdn.microsoft.com/en-us/library/dn133176%28v=sql.120%29.aspx
• ALTER SERVER CONFIGURATION – http://msdn.microsoft.com/en-us/library/ee210585%28v=sql.120%29.aspx