Improve the Report Query Execution Memory (Memory Grants)

[asavioliMSFT]

The report Query Execution Memory (Memory Grants) is based on the table tbl_Query_Execution_Memory, which is not returning the main queries allocating memory for query execution (memory grants). We should use the table tbl_dm_exec_query_memory_grants instead, which is based on the dmv sys.dm_exec_query_memory_grants.

Suggestion we have for the query used by the report

WITH CTE AS (
SELECT TOP 10
mg.sql_handle,
mg.plan_handle,
MAX(granted_memory_kb) AS granted_memory_kb
FROM dbo.tbl_dm_exec_query_memory_grants mg
GROUP BY sql_handle, mg.plan_handle
ORDER BY MAX(granted_memory_kb) DESC
)
SELECT
c.*,
(SELECT TOP 1 n.procname
FROM tbl_NOTABLEACTIVEQUERIES n
WHERE c.plan_handle = n.plan_handle) AS procname,
(SELECT TOP 1 n.stmt_text
FROM tbl_NOTABLEACTIVEQUERIES n
WHERE c.plan_handle = n.plan_handle ) AS stmt_text
FROM CTE c
ORDER BY c.granted_memory_kb DESC;