How to List Queries With Memory Grant and Execution Plan? – Interview Question of the Week #154

Question: Which current SQL Server requests have asked for a memory grant, and where can I see their text and plan?

Answer: Use sys.dm_exec_query_memory_grants for current grant requests, then retrieve their SQL text and XML plans using the corresponding handles. This was actually a question from my performance tuning workshop, not an interview. A customer had a long-running query they suspected was using too much memory. I wanted to see what it had requested, what it had been granted, and whether it was still waiting before debating whether the grant was the cause.

The original script went in the right direction but described the result too broadly as all currently running queries. This DMV returns requests with memory grants or requests waiting for them. Queries that do not require a grant are absent. It is also a live snapshot, not a history of completed requests.

SELECT mg.session_id,
       mg.request_id,
       mg.request_time,
       mg.grant_time,
       mg.wait_time_ms,
       mg.required_memory_kb,
       mg.requested_memory_kb,
       mg.ideal_memory_kb,
       mg.granted_memory_kb,
       mg.used_memory_kb,
       mg.max_used_memory_kb,
       mg.query_cost,
       mg.dop,
       st.text AS batch_text,
       qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
OUTER APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY mg.requested_memory_kb DESC;

Notice that SQL text comes from mg.sql_handle and the plan from mg.plan_handle. The text can be a whole batch rather than only the active statement. The plan is an XML plan associated with that handle, not a claim that this query captured a completed actual execution plan. A plan may also be unavailable, which is why the query uses OUTER APPLY.

The columns answer different questions. required_memory_kb is the minimum required to run; ideal_memory_kb is the amount estimated to fit the operation in memory. requested_memory_kb is the request, granted_memory_kb is the grant, and used_memory_kb and max_used_memory_kb help compare the grant with use. A null grant_time indicates a request still waiting for a grant.

This is one of the queries I keep in my consulting toolbox because it turns “that query takes a lot of memory” into something testable. A large grant is a clue, not a complete diagnosis; I compare it with the workload, waits, plan, and actual use before changing anything. If you have a useful variant of the query, share it in the comments and I will credit it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.