-- Find all queries waiting in the memory queue:
SELECT *
FROM sys.dm_exec_query_memory_grants
WHERE grant_time IS NULL
GO
-- Find who uses the most query memory grant:
SELECT mg.granted_memory_kb / 1024 AS granted_memory_mb,
mg.requested_memory_kb / 1024 AS requested_memory_mb,
mg.used_memory_kb / 1024 AS used_memory_mb,
mg.session_id,
t.TEXT,
qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC
OPTION (MAXDOP 1)
GO
-- Search cache for queries with memory grants:
SELECT t.TEXT,
cp.objtype,
qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist('declare namespace n="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //n:MemoryFractions') = 1
GO