Question: When reviewing some executions plans for slow running queries, I’ve noticed Memory Grant appearing. What is the Memory grant message?
Answer: As an example: When the SQL Optimizer predicts it will operate on 15 rows based on the latest statistics for those tables , a chunk of memory is granted to fulfil the operation.
But, if the statistics are inaccurate or not up to date, and the operation returns 10000 rows , the operation doesn’t automatically grant the extra memory. The operation will spill to disk – in the tempdb. Spilling to disk will allow the sorting and joining to progress the operation to the point of fulfilling the query. Spilling to disk is a much slower process than processing the rows in memory.
If the SQL Optimizer has more accurate information , it is able to make better decisions about granting memory.
The main point of memory grant is to maximise the query performance when the server is under a workload. By Granting the Memory , there is a decreased possibility, there will be an out of memory error.
The whole topic of where memory grant fits into the overall query lifetime is for another blog post.
Use this query to find all queries waiting in the memory queue. Resource Semapahore places new queries into a queue. When memory becomes available, a grant attempt is made . If enough memory is available, the memory is granted and the query starts running.
SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null