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

Question: How to List Queries With Memory Grant and Execution Plan?

Answer: Honestly, these question was not asked in interview but rather I was asked about this during my recent SQL Server Performance Tuning Practical Workshop. One of my customer always thought that one of the query which they ran often was taking too much memory. Additionally, the query was running for a long period of the time as well.

Here is the script which I provided them, which would like all they key element of all the running queries in SQL Serve.r

SELECT mg.session_id
,mg.granted_memory_kb
,mg.requested_memory_kb
,mg.ideal_memory_kb
,mg.request_time
,mg.grant_time
,mg.query_cost
,mg.dop
,st.[TEXT]
,qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY mg.required_memory_kb DESC;

When you run above query, it will show you all the currently running SQL Server queries. It will not show you historical information. the column granted_memory_kb displays how much memory was granted by SQL Server for that query to execute. Additionally the last two column demonstrates the query executed as well as execution plan for the query.

Well, the query is pretty simple but very powerful. I use it all the time during my consultation. Let me know if you have similar query in your toolbox. I would be interested to know your version of the query and if it has more useful information, I will publish it on the blog with due credit to you.

Reference : Pinal Dave (https://blog.sqlauthority.com)