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 )

Journey to SQLAuthority责编内容来自:Journey to SQLAuthority (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » How to List Queries With Memory Grant and Execution Plan? – Interview Question of the Week #154

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录