Hello Geeks and welcome to the Day 44 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
Till yesterday I have covered how to check statements, plans and their properties. Today I will be covering more about memory during execution of queries. Sys.dm_exec_query_memory_grants provides the information related to memory grants for queries.
Before seeing how to use sys.dm_exec_query_memory_grants, I will talk about how memory grants work. For a query to execute a compiled plan is prepared. Then an execution plan is built and then the actual execution of the instruction tree.
The memory consumers during query execution are compile, cache and memory grant. The compile memory is needed for building and searching the optimal query. This is short term memory and need only to compile a plan. Lack of this memory leads to bad plans or delay in compile. Cache memory is long term and helps in storing compiled plans. Pressure on cache memory leads to unwanted re-compiles. Memory grant is needed during execution to store temporary rows. They are needed for sort and hash joins.
Our area of interest which is not covered yet is memory grants. There are two parts to it. The required memory and additional memory. Required memory is the minimum memory needed to start the query execution. This is used for internal data structures and handle sort and hash joins. Additional memory is the amount of memory needed to save the temporary data based on cardinality estimation. This is called additional as the query can still run without this memory by storing this data on disk.
In case of Parallelism this memory required will increase. Additional memory is need for each worker for its copy of sort and hash join. Also it need memory for temporary storage of transferred rows. There will be no change in additional memory. When a memory grant is not satisfied it waits on RESOURCE_SEMAPHORE waittype. Also the grant_time will be NULL in sys.dm_exec_query_memory_grants.
Let us see how to check memory grants using sys.dm_exec_query_memory_grants. I will run two select queries on a table. One without any order and other with an order by. First I will get the SHOWPLAN XML and you will observe the below for these two queries
SELECT * FROM person.person GO
SELECT * FROM person.person ORDER BY NameStyle GO
The required memory is shown as “0” for the query without the order by clause. When you use order by the required memory is nonzero. Let’s confirm this from the output from sys.dm_exec_query_memory_grants.
SELECT * FROM sys.dm_exec_query_memory_grants
The requested_memory_kb is close to the estimated memory grant from the showplan. Even though the reserved memory is high the used memory is only 32 MB. This memory cannot be release during the query execution. So wrong estimation leads to bad memory usage. Also missing indexes and wrong key ordering also leads to performance issues.
Now you know how to check memory grants using sys.dm_exec_query_memory_grants. Tomorrow I will be covering one more execution related DMV. Stay tuned. Till then
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
