My last post about how much difference caching can make to a query left me thinking of other reasons that can cause a query to hand you a seemingly random set of execution times. This is one of the reasons I came up with… memory grants.
The two main blocking operators in an execution plan are SORT and HASH JOIN. These are the most common reasons a query will require a memory grant because neither operator can complete until it has all rows present and therefore it requires memory to store the rows whilst it performs its action.
To cater for this if SQL Server knows it will need this type of operator for execution it will place a request for memory. SQL Server will check to see what’s available and will allocate accordingly.
You can see current memory grant information by using the following DMV:
The main information that you are likely to be interested in are the following:
select session_id, requested_memory_kb, granted_memory_kb, ideal_memory_kb
This will show you the spid, the requested grant, how much memory was actually granted, and the ideal amount of memory that the query requires.
So how does this affect the running of a query?
A SQL Server doesn’t have unlimited memory and therefore let’s presume that you run the following query which requests a grant of 1.2GB:
from sales.SalesOrderHeaderEnlarged h
join sales.SalesOrderDetailEnlarged d
on h.SalesOrderID = d.SalesOrderID
order by d.rowguid
In this example my server has 14GB of memory spare… therefore you will receive your full allocation, the query starts executing immediately, and finishes in 25 seconds. All is happy in the world.
But what happens when you only have 1GB of RAM in the whole server? Well, in this case you’re in trouble. SQL Server will scale back the allocation for the query and you end up with something like the following:
In this example you can see that ideally SQL Server would like 1.2GB memory in order to run but that doesn’t exist in my test server (which I purposely restricted for this demo) so it has requested 1GB instead.
Luckily SQL Server can fulfil this request and therefore the query happily starts to run. However, because it hasn’t got its ideal allocation there is a downside to this…
If you were to profile the query you would see the above… sort warnings. This is because SQL Server didn’t have the required memory grant and therefore instead of being able to perform the SORT in memory it resorted to using tempDB. Yes… it will spill the sort to disk!!! Not what you want at all.
In this case my query finished in 35 seconds… all because of how slow tempDB is in comparison to RAM in my test machine.
Lastly… have you ever had those moments in which you have pressed execute but nothing seems to be happening? SQL is just sitting there and no results are coming out? Well, check your memory grants. It’s possible that your query really is doing nothing.
If you have a machine with just 1GB RAM and have 7 queries which, for example, require 100MB memory grant each then they will happily run and your server will sit there with 300MB remaining. So what happens if you then submit a query which requests 500MB memory grant?
Well the answer is actually “nothing”.
SQL Server will actually sit politely in a queue and wait for the full 500MB to be granted. There is a timeout value that can be best controlled in Resource Governor but I’ll not be going into that here, but in essence it will sit politely doing nothing and wait.
This can be seen by looking at the “grant_time” column of the DMV. If this is null then the query is politely waiting.
If the query hits its timeout then it will be granted the absolute bare minimum memory required to run. This will have catastrophic effects on the query duration, but it’s better than nothing.
If enough other queries finish so that a grant can be given then the query will take its grant and run as normal, albeit with a hefty startup delay which can be seen as the difference between “request_time” and “grant_time” in the memory grant DMV.
Therefore a query could take just 10 seconds to run, but if it ends up waiting for a memory grant then this could rise dramatically.
Memory grants cannot be controlled directly, they are created as a result of statistics. Therefore if you ensure your statistics are up to date then although you could occasionally see a delayed start to a query or two, you hopefully won’t see too many spills to disk.
There are a few things you can try to do to influence memory grants, but these are best left for another discussion.