SQL Server 2014 introduced us to a number of great new features, one that’s often overlooked but can quite easily improve the performance of read-heavy OLTP workloads, especially on resource constrained hardware, is Buffer Pool Extensions (BPE).
BPE allows you to extend a SQL Server’s buffer cache to non-volatile storage such as SSDs, which are often a fraction of the price of adding additional physical memory. Once BPE is enabled, it is used in a similar manner to normal buffer pool memory, however it is only used to cache “clean” pages, eliminating a potential risk of data loss due to storage device failure.
It’s simple to configure and doesn’t require any application changes. All you need to do is present the SSD storage to your server, and run the following TSQL command on your SQL Server 2014 Enterprise, BI or Standard Edition instance.
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'E:\MYBPEFolder\MYBPEFile.BPE', SIZE = 50 GB);
BFE’s are non-configurable, meaning that if you want to change things post implementation you will have to disable and re-create the BPE. To disable BPE, use the following ALTER SERVER CONFIGURATION command:
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
As far as sizing is concerned, the maximum size supported is 32 times max_server_memory, however Microsoft recommends a BPE of no more than 16 times. Also, don’t forget the two new DMV’s that let you query your BPE’s status and monitor its usage:
Cheers Phil @RoarSQL