3 Things to Know Before Using SQL Server Buffer Pool Extension

If you have read about new features introduced in SQL Server 2014, then you must have learned about the Buffer Pool Extension feature. This feature which will help DBAs to extend the Buffer Pool size beyond available physical RAM. One can easily compare this to the page file for an operating system – but it is even better.

Instead of adding additional physical memory to your database server, you just configure an Extension File on a SSD drive. Few people might think that adding buffer pool extension file would be the equivalent of extending RAM, which is not completely true. It should also be noted that there is no change required in the application to exploit this capability.

Here are three things which we should keep in mind before implementing this feature on a production server:

1. This feature is NOT useful for In-Memory OLTP: In SQL Server 2014 there is a new feature called In-Memory OLTP where we can keep table completely in RAM. You can read more about In-Memory OLTP feature at various sources. The data in in-memory table would always reside in physical RAM. For example if you have 50 GB table, can you have 16 GB RAM and 40 GB of BPE to achieve this? The simple answer is no and here is the reason: You can easily find that, in this feature there is no concept of 8 KB pages. The data which is saved In-Memory tables is not stored in traditional data files (MDF or NDF) but it’s stored in data and delta files. Since there are no pages, they would not come from buffer pool memory region and due to this the data for In-Memory table can’t be flushed to buffer pool extension files.

2. This is NOT a replacement of adding more RAM: Buffer Pool extension file can only keep data pages. There are many other memory sub-regions in SQL Server Memory and this feature can’t cater those. For example, the memory region which has cached query plans can’t be dumped to buffer pool extension file as it can contain only data pages. Also it should be noted that we will not see improvement in performance if the BPE file is kept on spinning media disks. It is recommended to should use SSDs. So you need to evaluate the cost of adding new SSD just to use this feature vs adding more RAM on the server. If you already have SSDs on the server then you can experiment with the feature and check if there is any improvement in the performance.
3. The size of the buffer pool extension file can’t be reduced: This is a very important consideration you need to keep in your mind that once the file has been created and the size of the file can’t be reduced. It is important to note that to change the size, we always need to disable the feature by using below command

 

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION OFF

and then enable it again with new size. If the new size is less than previous size, we would get this error:

Msg 868, Level 16, State 1, Line 4

Buffer pool extension size must be larger than the current memory allocation threshold <Size> MB. Buffer pool extension is not enabled.

Then the only way to get rid of the error is to restart SQL Service; doing that on production database server is not an option.

In summary, we can say that buffer pool extension introduces another level of cache. Whenever the page is evicted from buffer pool, it goes and sits in buffer pool extension file. The next request of the same page will not be a physical IO from the MDF/NDF file, but would be read from the BPE file, which should reside in SSD, which is faster than normal spinning based media. The latency and read/write speed of SSD is faster than traditional disks and hence would give performance improvement.