Memory grants and execution plans

28 July,2015 by Jack Vamvas

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

Read More on Memory Pressure

SQL WAIT TYPE – RESOURCE SEMAPHORE and how to reduce it ...

SQL Server – Min Memory Per query and performance - SQL Server ...

SQL Server Performance ,memory pressure and memory usage ...


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Memory grants and execution plans


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer