SQL 2014 is a great release from Microsoft and in this post I will describe information about Buffer pool extension in SQL Server 2014. In previous article, we have anylize new feature in SQL 2014.
As you know, the Buffer Pool is one of the main memory consumers in SQL Server. When you read data from your storage, the data is cached in the Buffer Pool. SQL Server caches Execution Plans in the Plan Cache, which is also part of the Buffer Pool. The more physical memory you have, the larger your Buffer Pool will be (configured through the Max Server Memory setting).
A lot of SQL Server customers are dealing with the problem that physical memory is restricted in database servers: all memory slots are already occupied, so how do you want to add additional memory to the physical server? Of course, you can migrate to a larger server, but that’s another story… The solution to this specific problem is the introduction of Buffer Pool Extensions in SQL Server 2014.
Before I’m talking about the configuration and the concrete use of the Buffer Pool Extensions, I want to talk a little bit about the architecture and design behind the Buffer Pool Extensions. The traditional Buffer Pool of SQL Server always differentiates between clean and dirty pages. A clean page is a page which has the same content as the page in the storage. A dirty page is changed in memory, but hasn’t yet written to the storage. Around every minute the so-called CHECKPOINT process writes dirty pages out to the storage, means that our dirty page becomes a clean page
The Buffer Pool Extensions itself are only used if the Buffer Pool of SQL Server get’s into memory pressure. Memory pressure means that SQL Server needs more memory than currently available. In that case the Buffer evicts pages from the Buffer Pool, which were least recently used. SQL Server uses here a Least Recently Used Policy (LRU). If you have now configured an Extension File, then SQL Server will write these pages into it, instead of writing them directly out to our slow storage. If the page is a dirty one, then the page will be also concurrently written to the physical storage (through an asynchronous I/O operation). Therefore you can’t loose any data when you are dealing with the Buffer Pool Extensions. At some point in time your Extension File will be also completely full. In that case SQL Server has to evict older pages from the Extension File (again through a LRU policy), and finally writes them to the physical storage. The Extension File just acts as an additional layer between the Buffer Pool and the storage itself.
Let’s have now a look on how we can configure the Buffer Pool Extensions in SQL Server 2014. SQL Server offers you here the command ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION. Let’s have a more detailed look on how you use it:
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(
FILENAME = 'd:\ExtensionFile.BPE',
SIZE = 10 GB
)
GO
The first restriction that you are hitting here is the fact that the Extension File must have at least the same size as the Buffer Pool itself. If you are specifying a smaller file size, you will get a lovely error message from SQL Server:
Msg 868, Level 16, State 1, Line 1
Buffer pool extension size must be larger than the current memory allocation threshold 1596 MB. Buffer pool extension is not enabled.
The next restriction that you will definitely hit is that you can’t change the size of the Extension File during the runtime of SQL Server. For example, when you want to change the Extension File to a larger size, you have to disable the Buffer Pool Extensions, and re-enable them again. You will have of course a performance degradation in the time of this operation, because you are just disabling one important caching layer used by SQL Server!
Be aware of this fact, when you are planning a deployment of the Buffer Pool Extensions for your production environment!!!
And in addition you are also not able to reduce the size of the Extension File, the file must be always larger than previously. Otherwise you are again getting an error message:
Msg 868, Level 16, State 1, Line 3
Buffer pool extension size must be larger than the current memory allocation threshold 4096 MB. Buffer pool extension is not enabled.
The whole configuration of the Buffer Pool Extensions can be also retrieved through the Dynamic Management View sys.dm_os_buffer_pool_extension_configuration.
When should you use the Buffer Pool Extensions? Microsoft makes the recommendation that your workload should be write-heavy, e.g. a OLTP workload. You should not have a look on the Buffer Pool Extensions, when you are dealing with a DWH/BI related workload – an Extension File doesn’t make sense here. And when we are talking about the Extension File itself, you should spend a very fast SSD for it! Traditional rotational hard disks are a No-Go for it!