SQL Query to Find Excessive Memory Grant

Sometime we need to check the memory used by current running query on a SQL instance.

Below is the query which I use during performance troubleshooting and it really helps me many a times. The query will provide you the current execution plan along with the memory granted, memory_used and memory requested by the query.

Query:

SELECT mg.granted_memory_kb, mg.session_id, t.text,mg.requested_memory_kb,mg.used_memory_kb,mg.query_cost, qp.query_plan,mg.plan_handle,mg.sql_handleFROM sys.dm_exec_query_memory_grants AS mgCROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS tCROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qpORDER BY 1 DESC OPTION (MAXDOP 1)

Hope you will find this useful :)