Cache pool Extensions in SQL Server 2014

In today's article I would like to talk about the cache pool extensions introduced in SQL Server 2014 (buffer pools Extensions). As we all know, in SQL Server, the cache pool is the primary memory consumer. When you read data from your store, the data is cached in the cache pool. SQL Server caches execution plans in the plan cache and is part of the cache pool. The more physical memory you have, the larger your cache pool will be (configured with the "max server memory" setting).

Many SQL Server users encounter a problem with limited physical memory in the database server: All memory slots are occupied, so how do you want to add additional memory to the physical server? Of course, you can move to a larger server, but that's another thing ... The solution to this particular problem is the cache pool extension introduced in SQL Server 2014. With the help of the cache pool extension, SQL Server introduces another layer in the memory hierarchy. Let's look at the following picture:

As you can see, at the top is the cache pool itself, it is very fast (according to response time (latency times)), at the bottom you will see our traditional storage, it is relatively slow. The cache pool extension is located just between 2 people-the traditional cache pool and our storage. The cache pool languish consists of a simple file (the so-called extension file), which should be stored on very fast storage-such as an SSD hard disk. The extension file is roughly the same as the Windows System's page file. Instead of adding extra physical memory to your database server, you can configure the extension on your SSD hard drive--just do it!

Before I discuss configuring and enabling cache pool expansion, I want to talk briefly about the architecture of the cache pool extension and the design behind it. SQL Server's traditional cache pool is always differentiated between clean pages and dirty pages. A clean page is the same page in memory as the content in the store. A dirty page is a page that changes in memory but has not yet been written back to storage. About every minute the so-called checkpoint (CHECKPOINT) process writes dirty pages back to storage, meaning that dirty pages become clean pages.

If the cache pool of SQL Server falls into memory pressure, the cache pool extension itself is used. Memory pressure means that SQL Server needs more memory than is currently available. In that case, the cache will evict the page from the cache pool, and those pages have just been used recently. SQL Server uses the least recently used algorithm (Least recently used Policy (LRU)). If you configure the extension file now, SQL Server writes these pages to the extension file instead of writing them directly to our slow storage. If the pages are dirty, these pages are also written concurrently to the physical store (through asynchronous I/O operations). So when you use the cache pool extension, you don't lose any data. To a certain point in time your extension file will be fully filled. In that case, SQL Server then expels the old pages from the extension file (also through the LRU algorithm), and finally writes them to the traditional storage. The extension file acts as an additional layer between the cache pool and the storage itself.

Now let's look at how to configure the cache pool extension in SQL Server 2014. SQL SERVER provides you with the ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION command. Let's take a detailed look at how to use it:

1  Use Master 2 GO 3 4 EXEC ' Show advanced Options ' 1 5 RECONFIGURE  with OVERRIDE 6 GO

 1  alter   SERVER CONFIGURATION  2  set  BUFFER POOL EXTENSION on  3   ( 4  FILENAME =    d:\extensionfile.bpe    ,  5  SIZE 1   GB  6  )  go 

The 1th limitation you will encounter here is that the extension file must be the same size as the cache pool itself, and if you specify a smaller file size than it is, you will receive the following error message from SQL Server:

MSG 868, Level A, State 1, line 1
Buffer pool extension size must is larger than the current memory allocation threshold 1596 MB. Buffer pool extension is not enabled.

Next you'll definitely meet the limit is that you cannot modify the size of the extension file while SQL Server is running. For example, when you want to modify the extension file to a larger size, you need to deactivate the cache pool extension and then enable it again. During this operation, your performance will degrade because you have just deactivated an important cache layer for SQL Server!

When you plan to deploy cache pool extensions for your production environment, you must be aware of this!!!

In addition you can not reduce the size of the extension file, the file must be larger than the previous. Otherwise you will receive the following error message:

MSG 868, Level A, State 1, line 3
Buffer pool extension size must is larger than the current memory allocation threshold 4096 MB. Buffer pool extension is not enabled.

The entire configuration of the cache pool extension can also be queried through the DMV sys.dm_os_buffer_pool_extension_configuration .

When should you use the cache pool extension? Microsoft recommends that your server workloads be less read-write (write-heavy), such as OLTP workloads. When you deal with DWH/BI related work review, you should not consider the cache pool extension--there is no point in enabling the extension file. And when we discuss the extension file, you should configure it for very fast ssd! Traditional rotating hard disk (mechanical hard disk) just forget it!

Cache pool Extensions in SQL Server 2014