With the introduction of the in-memory technology, we need to think about what are the new challenges to keep the service up and running. In this article I will explore one of the three key points to monitor in order to maintain your SQL Server healthy.
Last week I spoke at my local SQL Server user group, in Lisbon. The session was nice and the good feedback received encouraged me to write about this and share information that is not too spread over the internet (so far…): Monitor environments using In-Memory OLTP.
At the first look, nothing much different from the habitual is required, but if stop and think, this is not the same of monitoring “regular” SQL Server instances. The engine behind memory-optimized tables is not the same and the behavior of SQL Server is not the same in certain situations.
When I was thinking about what to add to my presentation, I found three key points that are the weaknesses of the In-memory OLTP. Based on this, I started to test some scenarios, in order to prove that those were a weak points indeed….
During this article I’m going to talk about each one of those three key points, as well as show a solution to monitor.
If you are curious, my selection of key points to monitor is:
- The server/OS memory.
- Checkpoint files.
- The buckets (from hash index).
I’m starting with “the server memory”. In the following articles we will talk about the other key points.
Case #1: Server Memory
As we know, SQL Server uses memory to operate, like any other Windows process, even when the In-memory feature is not being used, right? By the way, talking about “Windows Processes”, have you thought what might happen when you don’t have control over the Operating System? This situation is very common, there are some companies where the DBA is just responsible for the databases, and a team of system administrators are taking care of the server and Operating System.
What is the problem with that? Actually, have a team dedicated to each technology is good! As long as the teams are communicating with each other and share the knowledge. If everyone is limited to "their own farms", the problems shall start to happen…Talking about our case, the main concern is what will be installed into the Operating System, beside SQL Server. The more applications/processes are installed, the more is the concurrency for memory allocation.
Some companies have a default installation, including anti-virus software, firewall, and other applications and services. Another possibility is to have the SQL Server and the application running on the same OS…something very far from any best practice.
So, what is going to happen? In the following image, we have a very simplified memory allocation image. You are free to imagine the total size of total memory available 🙂
Notice that I distinguished the SQL Server used memory from the In-Memory OLTP in order to better pass my point.
We can observe a large amount of free space in memory, comparing with the other memory allocations, which is good. Anyway, after a while, SQL Server is working, the data in memory-optimized tables are being modified, new rows are being inserted… And we have this new memory allocation map:
We can see that both “memory-optimized tables” (In-memory) and “OS Processes” are now using much more memory comparing to the previous image.
At this stage, an alarm should be triggered in order to warn about the short free memory space available, as the probability of fill out all the memory is now very high.
We could expect this growth coming from the In-Memory component, but OS Processes grew abnormally… After some time, we took a snapshot of the memory map and this is what we got:
Now there’s no free memory at all! Why? Again, because of an unexpected memory use from other OS Processes. It’s not easy to reach this scenario, it depends of some factors. Also the OS will always try to free up some memory, by paging out everything that is possible.
Another common situation is start using the In-Memory OLTP and don’t limit the memory usage. This can bring an obvious problem… It’s possible to limit the memory used by memory-optimized tables, and this can be achieved using the Resource Governor.
Going back to the “full memory” scenario, what happens here? The server will start not responding. If you are connected using RDP, you feel the first shiver when you lose you session and aren’t able to reconnect 🙂
Summarizing:
- Problem: Low available memory and concurrent processes.
- What to monitor:
- Free Memory percentage.
- The other processes (all but SQL Server ones)
- Side effects:
- Operations on memory optimized tables start to fail.
- Server stop to respond.
How to monitor?
Free Memory percentage
The use of PowerShell is perfect for this! The following script will return an error if the free memory space is less than the set threshold:
Memory used by other processes
As said, sometimes we plan the server with the memory we really need, but we cannot foresee the higher memory utilization of other OS processes. This could be caused by many reasons… starting from a mistake when the amount of needed memory was defined, during the project phase, to simply have tools installed to the server that you was not expecting (surprise!!!).
To monitor this, I’m using PowerShell again. This script is a derivation of the first one, and it returns an error message when the OS processes (excluding the SQL Server ones) are using a memory percentage higher than the defined by the threshold.
In this article, we checked one of the three key points to take care for In-Memory OLTP environments “the server memory”. Showed the possible side-effects and how to monitor, in order to avoid such case. Remember that the monitoring help us to be proactive… I’m sure that you won’t like if your pager beeps when you are relaxing in a paradisiac island, right? Avoid being reactive 🙂
Keep following the blog, as I will publish articles talking about the other two key points very soon! See you soon!!
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