Memory grants, now here’s a fun thing that can pretty much take your SQL Server to its knees.
The Symptoms
The first thing that you’re going to notice is that your SQL Server is going to be running slowly and I mean, slooooooow!
When I’ve got a server on a serious go slow, the first thing that I like to look at is the wait stats, I want to know what SQL’s waiting for.
dm_os_waitstats holds all the information that you need on wait stats, the downside however is that it gives you a running total since the server was started. That can be useful for some things but isn’t going to be great when we want to know exactly what’s going on now.
There are a few scripts out there that you can use for this, Brent Ozar’s sp_blitzfirst ( https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit) is a great go to (it will also give you details of any outstanding memory grants, very relevant to this particular post) but I tend to use a modified version of Paul Randal’s wait stats script.
Any monitoring tool will also give you this information.
If you’ve got an issue with memory grants then you’re very likely to see RESOURCE_SEMAPHORE waits pretty high up on your wait stats list.
These aren’t something that I ever want to see and if I do I am straight away going to go looking for the cause. Basically what’s that’s telling you is that you’ve got processes waiting on a memory allocation. This is going to be because SQL Server hasn’t got enough memory to dish out to that particular process.
What’s going on here is that every time a query runs, it’ll ask for a certain amount of memory. A happy SQL Server will serve up a tasty slice of memory for that query to run in. The problem comes when the query is asking for a bigger slice of the pie than SQL has available. In that case the query will need to wait until SQL has enough free to give the query what it wants, that’s when the RESOURCE_SEMAPHORE wait starts ticking up.
We can look a little deeper into this by querying sys.dm_exec_query_memory_grants to see exactly what has already been granted and what is waiting for a grant
SELECT * FROM sys.dm_exec_query_memory_grants ORDER BY granted_memory_kb DESC
This looks like a bad situation for a SQL Server to be in, there are a few things that catch my eye straight away.
Firstly, we’ve got a large number of processes with a grant_time of NULL. That means the process is waiting on a memory grant, that goes hand in hand with the RESOURCE_SEMAPHORE waits that we’ve been talking about. As soon as you see this going on, it’s a warning sign that SQL Server is in a spot of bother.
The next question is why hasn’t SQL Server got a big enough slice of the pie to give to these poor processes. My eye now turns to the top of the table and the granted_memory_kb column.
WOW, check out those top 3 rows, those processes have got around about a 32GB memory grant each, that’s 96GB granted to just those three queries.
Interesting Fact: Each of those grants happens to be 25% of SQL’s query memory. SQL Server will cap a query’s memory grant to 25% to prevent a single query for taking all the memory.
Unfortunately, in our case we’ve got three process all hogging a 25% slice. That means that SQL’s effectively running on 25% of it’s query memory, there’s not much of that pie left to go around, no wonder it’s struggling.
Another thing that I’ve noticed is the grant_time, two of those queries have been holding their grants (total of 50% of the server’s query memory) for a week! There’s some real filth going on there.
Further Investigation
Now that we’ve identified the problem processes we can do a little more digging if we wanted to. Personally, in this particular case I knew exactly what those processes were and happily killed them off but you might want to find out more.
sys.dm_exec_query_memory_grants gives us some handy information, we’ve got the SPID so it’s easy enough to figure out who’s running the query.
We also get the plan handle and the query handle, pop these into the following queries and you can get the query text and the execution plan.
SELECT text FROM sys.dm_exec_sql_text(<plan handle>)
SELECT query_plan FROM sys.dm_exec_query_plan(<plan handle>)
Thanks for reading and I hope that if you’re ever battling memory grant problems, you’ll find this useful.