Working with Buffer Pool Extension

There are many limitations to the SQL Server Standard Edition Database Engine.  In my opinion, one of the most confining is the memory limitation.  Many of us know the Page Life Expectancy counter, but why is this important?  For SQL Server to deliver data to you, the data is read from 8K pages on disk to 8K pages in memory.  If our systems are limited by memory, this will cause SQL Server to release unused data pages from memory and read new ones from disk.   Disk I/O is a heavy bottleneck.  A wonderful and easy answer may be to “add more memory”.  If your organization is using SQL Server Standard edition, there is a limit on how much memory the SQL Server Database Engine will use.  For SQL Server 2008R2, that limit is 64GB of memory.  For 2012/2014/2016, that limitation is raised to 128GB of memory.  If you have Standard Edition, adding memory may not be an option.

Our choices prior to SQL Server 2014 would be to upgrade to Enterprise Edition or deal with it.  As of SQL Server 2014, we have another option.  In my first part of the “Blue Collar SQL Series”, I’m going to discuss Buffer Pool Extension.  Buffer Pool Extension(BPE for the remainder of this article) was introduced in SQL Server 2014. blah

Buffer Pool Extension Described

With this feature, SQL Server will extend the Buffer Pool Cache to non-volatile(ssd) storage.  This will alleviate the I/O contention of mechanical disks by augmenting memory.  The BPE uses the SSD as memory extension rather than disk.  This feature can be used with standard and enterprise, but would provide noticeable benefits for Standard Edition.  According to books online, the BPE size can be up to 32 times(Enterprise) or 4 times(Standard Edition) the value of max_server_memory, but the recommended ratio is 1:16 or less.

By utilizing this option, we can alleviate some memory pressure.  To demonstrate this for me was a litte difficult at first. My laptop, as most newer laptops, has a SSD. So I plugged in a SATA hard drive externally and moved my database there for testing. If the database files are already on SSD, adding BPE may not give much benefit as the memory from BPE would write to SSD as well.

In order to truly mimic memory pressure, I lowered my max memory setting. Below is the code to reconfigure memory and enable BPE.

/********************************************************************
** Lower memory
********************************************************************/
EXEC	sys.sp_configure N'show advanced options', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC	sys.sp_configure N'min server memory (MB)', N'512'
GO
EXEC	sys.sp_configure N'max server memory (MB)', N'700'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC	sys.sp_configure N'show advanced options', N'0'

/********************************************************************
** Turn on BPE
********************************************************************/
if	exists
(
select	1
from	sys.dm_os_buffer_pool_extension_configuration
where	state = 0
)
ALTER	SERVER CONFIGURATION
SET	BUFFER POOL EXTENSION ON
(FILENAME = 'C:\Databases\BPE\Wolf_Primary_BPE.BPE', SIZE = 3GB)
GO

After this, I used a tool written by Mr. Adam Machanic(b|t) called SQLQueryStress. This lovely tool allows you to span the same query across multiple SPIDs and run several times. In each of these tests, we are running 5 iterations twice.

Spinning Disk, Ample Memory

First, we show the results of the query on a SATA spinning disk with a max memory setting of 5GB.

We see that running in this scenario takes 1 minute and 53 seconds. Not great, but we do have ample amount of memory.

Spinning Disk, Limited Memory

Next, let’s see what limiting the memory does with our testing.

This run takes much more time to complete due to the much higher disk I/O with the lack of memory. From a little less than 2 minutes to 13 minutes total, 6 1/2 minutes per iteration. This shows just how important memory can be.

Spinning Disk, BPE enabled

Now we shall see the benefits that having BPE enabled can offer.

We see that we have gone from 1 minute 53 seconds, to 13 minutes and now to just under 5 minutes.

SSD, Ample Memory, BPE Disabled

In a perfect world, we will have SSDs and ample memory everywhere! 5GB of memory and SSD takes our scenario to 24 seconds total.

Conclusion

While there is no replacement for the speed of memory, BPE is a viable workaround when memory cannot be added.