Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A DMV a Day – Day 22

The DMV for Day 22 is sys.dm_exec_query_memory_grants, which is described by BOL as:

Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute. Queries that do not have to wait on a memory grant will not appear in this view.

This DMV allows you to check for queries that are waiting (or recently had to wait) for a memory grant. This particular DMV works with SQL Server 2005, 2008, and 2008 R2. There were some new columns added for SQL Server 2008 and above. It requires VIEW SERVER STATE permission.

-- Shows the memory required by both running (non-null grant_time) 
-- and waiting queries (null grant_time)
-- SQL Server 2008 version
SELECT DB_NAME(st.dbid) AS [DatabaseName], mg.requested_memory_kb, mg.ideal_memory_kb,
mg.request_time, mg.grant_time, mg.query_cost, mg.dop, st.[text]
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
ORDER BY mg.requested_memory_kb DESC;


-- Shows the memory required by both running (non-null grant_time) 
-- and waiting queries (null grant_time)
-- SQL Server 2005 version
SELECT DB_NAME(st.dbid) AS [DatabaseName], mg.requested_memory_kb,
mg.request_time, mg.grant_time, mg.query_cost, mg.dop, st.[text]
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
ORDER BY mg.requested_memory_kb DESC;

Ideally, you would want to see few, if any rows returning from this query. If you do see many rows return as you run the query multiple times, that would be an indication of internal memory pressure. This query would also help you identify queries that are requesting relatively large memory grants.

Comments

Posted by Anonymous on 22 April 2010

Pingback from  Dew Drop – April 22, 2010 | Alvin Ashcraft's Morning Dew

Posted by Jason Brimhall on 22 April 2010

Another great script.  I can use this one right away.

Thanks.

Posted by Dukagjin Maloku on 23 April 2010

Once again very nice script, thank you!

Posted by Adam Machanic on 25 April 2010

Glenn,

This is not quite correct. The view reports ALL workspace memory grants, regardless of whether a wait occurred. So merely seeing rows here is not indicative of memory pressure. To see waits, you need to compare request_time and grant_time -- or if grant_time is NULL, that means the query is still waiting. So to detect actual pressure you'd want to add a predicate like:

WHERE request_time < COALESCE(grant_time, '99991231')

Posted by Glenn Berry on 25 April 2010

Thanks for the clarification and correction. I will update the entry on my blog, but I don't think SQLServerCentral will pick it up on the syndicated feed.

Leave a Comment

Please register or log in to leave a comment.