Out of memory? Use the sys.dm_exec_query_memory_grants DMV to find the heaviest query running right now and kill it.

By memory usage:

SELECT session_id, (requested_memory_kb/1024.) AS [Requested Memory (MB)], grant_time, query_cost, text
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
ORDER BY requested_memory_kb DESC

By query cost:

SELECT session_id, (requested_memory_kb/1024.) AS [Requested Memory (MB)], grant_time, query_cost, text
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
ORDER BY query_cost DESC

Labels: SQL Server, SQL Server - Diagnostics, SQL Server - Scripts

Read the article.

This entry was posted on Thursday, October 18, 2007 at 12:56 PM.