See Also: Main_Page - Transact SQL Code Library - Memory Performance Tuning

This query returns queries currently waiting on memory along with the query plan. It's a useful thing to check when a fast server becomes slow.

T-SQL Script

 select text
, query_plan
, requested_memory_kb
, granted_memory_kb
, used_memory_kb 
from sys.dm_exec_query_memory_grants MG 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  
CROSS APPLY sys.dm_exec_query_plan(MG.plan_handle)

Query Test Checklist

• Works on SQL Server 2008: Yes
• Works on SQL Server 2005: Yes
• Works on SQL Server 2000: No
• Works on Standard Edition: Yes
• Works on case-sensitive servers: Yes

Tests Updated by Brent Ozar, 2009-04-01