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.

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

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)

Quest

SQL DMV, SQL Memory, SQL Scripts, SQL Server
Previous Post
How to List All ColumnStore Indexes with Table Name in SQL Server? – Interview Question of the Week #153
Next Post
How to List All the Nonclustered Indexes of Your Table? – Interview Question of the Week #155

Related Posts

2 Comments. Leave new

Leave a Reply