Introduced on SQL Server 2014, the Buffer Pool Extension came to build up a new layer of cache, helping to maintain the “hot” pages accessible with less effort. With the introduction of Hekaton, the BPE has also an important role. In this article we will explore all the aspects of this new feature as well as understand how it works.
With the release of SQL Server 2014, earlier this year, some new features were released, with a special remark to the In-memory OLTP (Hekaton). Together with that new feature, new challenges were brought. The available memory is, of course, one of the main challenges. More ahead, we will understand better why.
Buffer Pool Extension is another new feature on SQL Server 2014. The essence behind this is simple: A file is created in a fast disk (SDD) in order to extend the Buffer Pool size – as the feature name suggests – taking advantage of a fast disk, which results in a improvement of the instance performance. This capability allows the buffer pool to retain more “buffers”, avoiding an extra paging activity. Basically, the Buffer Pool Extension will be another level of cache. Not so efficient as the memory – in the end of the day this is a SSD disk, not a RAM memory – but way better than access the disk to get the pages.
If you quickly search on Google about Buffer Pool Extension, you will find some information, not too many results, but much of the same. On this article I will try to go more deep into this feature, showing how it works and what’s the behavior of SQL Server when you have Buffer Pool Extension enabled.
The main question that I always hear is: “For what is this used for?”. The main purpose of the Buffer Pool Extension is misunderstood as a way to give “the last chance” to that old server that we have, but is already very limited to run the current workload. This doesn’t make sense.
Let’s think: If we have a server with memory limitations, it would make sense to enable the Buffer Pool Extension, in order to extend the available memory and improve the performance, right? Yes. But wait! The Buffer Pool Extension feature is… a SQL Server 2014 feature! Why would I install a SQL Server 2014 instance in an old and limited box?
You can do this, is up to who is architecting the infrastructure, and I believe that there are valid possibilities to this scenario. Anyway, the main idea of Buffer Pool Extension is not extend the lifetime of an old server. There’s more behind this.
About the Buffer Pool
Before start to dig into the Buffer Pool Extension itself, we need to understand more about the Buffer Pool purpose and how it works, in a very high level. If you already know for what the Buffer Pool is used for, you can skip this part and go to the next section.
When a SQL Server instance is started, a range of virtual addresses, in memory, is assigned to its assigned process. This range of addresses has a name: Virtual Address Space, or simply, VAS. Inside of the SQL Server VAS we can distinguish two main areas: the Buffer Pool area and the other components. But why generalize the “other components” and remark the buffer pool? Because the Buffer Pool is the main component, using most of the space in the VAS of SQL Server.
What’s the objective of the Buffer Pool? Being direct, the Buffer Pool’s role is to minimize disk access, to speed up SQL Server performance. SQL Server files are organized in 8Kb pages, and anytime a page is requested, by a SELECT query i.e., SQL Server first checks if this page (Buffer) is in the Buffer Pool, if not, it will access the disk, copy the page to the Buffer Pool and than return the page handler to the requester. Plan Cache, Lock Structures and Buffer Cache are examples of structures contained in the Buffer Pool.
The reason behind all this work is because the memory access is way faster than disk access. Remember, Buffer Pool is part of SQL Server’s VAS, so it lives in the memory.
Now that we understand the page flow, and the purpose of the copy from the disk to the memory, we can predict that the more pages we have in the buffer pool, the better it is for the instance’s performance. At this stage, we also find a problem: The space in memory is finite. At some point the Buffer Pool will be packed out… What happens now? SQL Server will start to free up space, by evicting pages from the Buffer Pool. Those pages are evicted based on a policy responsible to select pages to be evicted in order to free space, but still keeping in the Buffer Pool, pages that have a high probability to be requested again in a near future.
Size Definition
The instance administrator influences in the Buffer Pool size. Sometimes I see a misconception about the Maximum and Minimum server memory settings on instance’s properties.
Till SQL Server 2008 R2, when we define min and max server memory, we are defining precisely the size of the Buffer Pool. From SQL Server 2012 this changed. Earlier versions of SQL Server are now including the Multi-Page Allocations (MPA) and CLR Allocations into this range, which means that we need to rethink the way to define the minimum and maximum server memory settings, knowing ahead what we will host in the instance we are administering.
Clean vs. Dirty
We can find two kinds of page into the Buffer Pool – Clean Pages and Dirty Pages. When a page is copied from the disk into the memory, its first state is always Clean. Once modified, it’s marked as Dirty.
There’s an effort to keep the less amount of Dirty pages as possible, in the buffer pool, in order to minimize data loss if a crash occurs. As the memory is volatile, SQL Server have three processes that are periodically writing dirty pages back to the disk, namely the Lazy Writing, Eager Writing and Checkpoint processes.
We reviewed the role, the behavior and challenges of the Buffer Pool, we are ready to understand how Buffer Pool Extension works, but before, I will introduce another component, that makes the Buffer Pool Extension makes sense – the In-Memory technology. In the next part of this series, we will understand how In-Memory OLTP (Hekaton) works, in general, and the memory challenge that justifies the use of the Buffer Pool Extension.
With experience working in Portugal, Holland, Germany and United Kingdom, he's always available to learn and share his knowledge, in order to contribute to SQL Server community,
View all posts by Murilo Miranda
- Understanding backups on AlwaysOn Availability Groups – Part 2 - December 3, 2015
- Understanding backups on AlwaysOn Availability Groups – Part 1 - November 30, 2015
- AlwaysOn Availability Groups – Curiosities to make your job easier – Part 4 - October 13, 2015