In continuation of the previous post “Buffer Pool Extension (BPE) – Introduction to the Buffer Pool”, we will keep diving into the Buffer Pool Extension, this time, talking about another important factor behind this feature – the memory. No, we will not review how the memory works literally, we are talking about the great SQL Server 2014 new feature: The In-Memory OLTP, or the known HEKATON.
But … Isn’t this a series about Buffer Pool Extension? Yes it’s, anyway the Buffer Pool Extension and In-Memory technologies are complimentary. This way, let’s understand better about In-Memory OLTP and see how the Buffer Pool Extension can help instances with this feature.
To add more context, for who never heard about the In-Memory OLTP, we will review from where this came from and its benefits.
Let’s start from the hardware price: A notable memory price reduction was observed, which turned the Mb price very accessible. In the following chart we can understand this reduction pattern since the year of 2000:
Effectively, since 2008 the memory price is more stable, with values around $9 per 100 Mb. In other hands, the evolution of CPUs changed: Before we experienced a fast growth of the clock speed (MHz), nowadays this is stable. But during the same period that clock speed reduced its evolution, and the focus passed to be the number of cores. Since 2005, the relative performance of a CPU is still improving, but not in the same speed as before.
Recognizing this trend, Microsoft started to build an engine optimized to work with large amount of memory and multi-core CPUs. That was the beginning of the Hekaton project, today known as In-Memory OLTP.
Microsoft released the In-Memory OLTP on SQL Server 2014, but in fact, this has been developed for a longer time. In terms of curiosity, “Hekaton” is derived from the Greek work “ἑκατόν”, which means” Houndred”. The explanation of this is based on its objective: Reach a performance 100 times better than what we have on the regular SQL Server engine. Currently we have reached a value around 30 times faster, which is already VERY GOOD. The goal is to keep improving.
The In-Memory OLTP was integrated into the SQL Server engine, so no new licenses are needed implement this.
How this really works?
I’ve heard people saying that SQL Server already put “things in memory”, so “we have no advantage in having the In-Memory OLTP working”. In fact, put “things in the memory” is the natural behavior of SQL Server, and we even had the DBCC PINTABLE… But the In-Memory OLTP goes beyond this, as an entire new engine was created – looking to reach the best performance.
A research was made, and was identified that 80% of the execution time is spent in the Storage Engine and Relational Engine.
Based on this, two components were created: Memory-Optimized Engine and Compiler. Those components are an important part of the new memory-optimized engine.
The relation of In-Memory OLTP and Buffer Pool Extension
The In-Memory OLTP itself worth an entire series, so let’s go to the point here: Buffer Pool Extension. What’s the real challenge, looking to the memory point of view, and in what the Buffer Pool Extension can help us in order to have a stable environment?
The memory utilized by the In-Memory technologies are out of the Buffer Pool scope, so it falls in the “others” category (remember the part 1?), in the SQL Server’s Virtual Address Space (VAS).
If you don’t remember or need more details about the SQL Server’s VAS, I invite you to take a look in the first article of this BPE series.
Memory Optimized Tables have priority in the memory allocation, so what happens when it grows? Let’s say that it filled all the available free space:
From that moment, the Buffer Pool won’t have room to growth, and as a result of this, a lot of paging may start to happen resulting in a wave of bad symptoms.
If we look for that scenario, we can find the first benefit and great utility for the Buffer Pool Extension. Instead of go to the disk, all the time that a page is needed, we would access the Buffer Pool Extension file, which is supposed to be placed in a fast disk. The Buffer Pool Extension won’t resolve the root problem, but it will minimize a lot the impact.
Going ahead, to better understand, what if the space utilized by the Memory Optimized Tables keep growing? As said, the Memory Optimized Tables have priority over the rest. The following image reflects pessimistic scenario that we can experience:
As we can see in the image, the size of the Memory Optimized Tables is increasing and taking all the available free space. Even with no more free space to take, it continues to grow, because it has priority over the other components. As a result of this, the Buffer Pool has a minimum space to operate, making the number of “page-in” and “page-out” operations increase significantly, and consequently accessing more the disk in order to get the needed pages. As side effects of this are a general slowdown of the workloads and transactions on the Memory Optimized Tables failing because of the out-of-memory situation.
It’s possible to avoid this to happen, and we have few options:
- Monitor your system and anticipate this situation.
- Adequate the memory size accordingly, having enough room to fit all that you need in the memory.
- Use the Resource Governor in order to limit the memory consumption.
- Enable the use of Buffer Pool Extension (BPE).
As you can notice, from the four available options, half are passive solutions, by simply monitoring and defining your server hardware properly, and the other half is using two active solutions, by using SQL Server features to interfere directly in the system in order to avoid the above scenario to happen.
This article was the second part of the Buffer Pool Extension series, but in the end of the day was a “mix of things”. We introduced the In-Memory OLTP, we understood the relation of the Memory Optimized Tables with other memory components and also shown few options to avoid memory problems when having the In-Memory OLTP in utilization. One of the options here is use the Buffer Pool Extension, which together with the Resource Governor are the best option to prevent the exposed problem.
In the next article we will (finally) dive into the Buffer Pool Extension, understanding how it works and how to enable the feature. Thank you for reading!
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