Published on December 22nd, 2014 | by Alejandro Cordero8
SQL Server 2014 New Feature: Buffer Pool Extensions
SQL Server 2014 ships with exiting new features, buffer pool extensions is one of these and it promise to be a very useful addition to your DBA toolbelt. In an initial SQL Server 2014 new features article we mentioned this topic and is time to describe in detail how to use it.
What is BPE?
The idea behind a buffer pool extension is very simple, it is a way to tell SQL Server memory where to page memory if needed, this means when SQL Server runs out of memory it will normally page into disk, which is extremely slow, instead you can create a buffer pool extension and point to a high performance device to decrease the impact when you hit limits of your memory usage.
This is a workaround for when SQL Server constantly runs out of memory and start having performance issues, when this happens normally you try to get some budget approved and add some more memory, start doing some performance tuning or plan for a migration, all these are the recommended way to go, however none of these happen easily in a organization, so you need a band aid in the mid time so you can get your application functional and shiny!
If by any chance you have some SSD, Fusion-IO or any other device available that you can plug into your database server and make it available to SQL Server 2014, then you can enable a buffer pool extension. It works just like adding a new datafile.
You can enable a new buffer pool extension and place it where you want, the only requirement is that the path you are using is available to SQL Server and that the SQL Server Service user has full read/write permission over the specified disk path.
HOW TO ENABLE BPE?
So Start by checking your memory and check your limits
USE master GO EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure 'max server memory (MB)' GO
Then enable your buffer extension by running below command
USE master GO ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = F:\MY_Cache\SQL2014_newFeature.BPE', SIZE = 15 GB); GO
The obvious idea here is to point to a device that will have a good performance, SQL Server won’t validate that in fact you are using the proper device, so basically you can point to any disk, if you do so the magic can disappear as your performance will still suffer if you start paging into old spinning disks.
Having this option enabled and pointing to a high performance device is a lot better than not having it at all, if you ever run out of memory and don’t have anything in place you will suffer from memory paging, memory pressure and all sorts of nasty things, however this is NOT better than having enough Memory from the first place.
Doing the old capacity planning and estimate in advance system usage and growth is still better, try to be proactive about this and be sure to allocate proper amounts of memory in advance, but still having a backup plan like BPE won’t hurt.
How to Modify BPE?
You can turn off BPE just by running below command:
USE master GO ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF; GO
If you want to modify the running value of BPE, you need to turn it off, modify the physical file size or location and turn it on:
USE master GO ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF; GO ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'F:\MY_Cache\SQL2014_newFeature.BPE', SIZE = 10 GB); GO
As a summary please follow below checklist when using this feature:
1-Enable Buffer Pool Extension and point into a high performance device , SSD drives, Fusion-IO and alike.
2-Buffer Pool Extension is not a substitute of Memory, having enough memory is always better
3-Do not point your BPE into a drive that contains datafiles, logfiles, tempdata, backups, so on. If possible reserve this location only for this purpose.
4- Test this feature in pre-prod/QA first, also do not just enable/disable during production hours as you might get odd behaviors depending on your memory usage and system specifics, check this MS best practice:
Test the buffer pool extension thoroughly before implementing in a production environment. Once in production, avoid making configuration changes to the file or turning the feature off. These activities may have a negative impact on server performance because the buffer pool is significantly reduced in size when the feature is disabled. When disabled, the memory used to support the feature is not reclaimed until the instance of SQL Server is restarted. However, if the feature is re-enabled, the memory will be reused without restarting the instance.
5- Do not just size your BPE to a huge random size, follow below MS recommendation(this is important, following this ratio does impact the throughput of your BPE):
The buffer pool extension size can be up to 32 times the value of max_server_memory. We recommend 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. A lower ratio in the range of 1:4 to 1:8 may be optimal.
6-BPE does have an important restriction, the allocated size must be greater than the current memory allocation threshold. Otherwise you will get below error
Msg 868, Level 16, State 1, Line 8
Buffer pool extension size must be larger than the current memory allocation threshold 8104 MB. Buffer pool extension is not enabled.
In the example above , SQL Server max memory was set to 8Gb and I tried to only add 2GB as BPE.
Hope you like this feature, remember to follow best practices and like most of SQL Server Features, test, test, test. Cheers!