SQL Server 2014 : Buffer Pool Extension

Following on from my post last week on SQL Server 2014 : What’s New I am having a little deeper look into the Buffer Pool Extension (BPE). The introduction of the BPE, which is available in Standard Edition and Enterprise Edition allows you to extend your available memory by providing a hybrid buffer pool to help reduce I/O bottlenecks as long as you have access to an SSD.

Now that we know this is what the BPE is lets have a look more closely at what does this mean for our instances of SQL Server.

Prior to SQL Server 2014, the management of data and index pages in the buffer pool was handled by the buffer manager. This has not changed with the release of SQL Server 2014, a change in the architecture has allowed for creation of a hybrid buffer pool. The hybrid buffer pool now consists of the existing buffer pool plus an extension that resides on nonvolatile storage or an SSD. The buffer manager still deals with identifying those pages in the buffer pool that can be expunged, when required pages for a request are not residing in memory but on disk. This practice is still handled and undertaken by the buffer manager. In addition to the buffer manager deciding on which pages should be in the buffer pool or not, it is now identifying those pages which are considered as “clean pages” and migrating those pages out of the buffer pool onto the BPE to allow for even more data to reside in the buffer pool. This practice is providing the following benefits to your SQL Server environment (as stated in the Microsoft BPE article) :

• Increased random I/O throughput
• Reduced I/O latency
• Increased transactional throughput
• Improved read performance with a larger hybrid buffer pool
• A caching architecture that can take advantage of present and future low-cost memory drives

Because the memory manager only places clean pages into the BPE, there is no risk of data loss in the event of a disk issue or restart of the instance. Your data is safe.

To utilize this new feature it is very simple to implement. Inside SQL Server Management Studio the following script can be modified to your requirements.

ALTER SERVER CONFIGURATION

     SET BUFFER POOL EXTENSION ON (

          FILENAME = '[FULL PATH TO SSDMyBPEFile.BPE',

          SIZE = 8 GB -- Minimum size should be 4 x of your Max Server Memory

);

There is a new Dynamic Management View (DMV) sys.dm_os_buffer_pool_extenstion_configuration that you can use to confirm the configuration details in your environment. The different states your BPE could be in are :

1. 0 – Disabled
2. 1 – Disabling
3. 2 – Reserved for future use
4. 3 – Enabling
5. 4 – Reserved for future use
6. 5 – Enabled

Once you have enabled BPE, if you need to either increase or decrease the size of your BPE you will have to disable BPE and then re-enable with the new size. Disabling BPE for this will have an impact on your environment as all of the pages residing in the BPE will need to be retrieved from disk, thus increasing your I/O utilization. If you need to reduce the size of your BPE, there are extra considerations you need to take into account. The BPE size can not be changed to a value smaller than the existing combined buffer pool size. To achieve a smaller size, BPE will need to be disabled, followed by a restart of your instance to release the memory prior to you being able to enable it again.

To disable BPE you can use the following:

ALTER SERVER CONFIGURATION

     SET BUFFER POOL EXTENSION OFF;