SQL Server Memory Grants

DMV Query to examine the cached query plans that are taking up memory space

The query below tells us which SQL is using what amount of memory currently. This space is used to for the duration of the query processing only and does not include the space occupied by compiled plans in the cache

SELECT
session_id
, granted_memory_kb
, syst.text AS sql_text
, sysp.query_plan
FROM sys.dm_exec_query_memory_grants sysm
CROSS APPLY sys.dm_exec_sql_text (sysm.sql_handle) syst
CROSS APPLY sys.dm_exec_query_plan (sysm.plan_handle) sysp
ORDER BY 1 DESC

DMV Query to see which queries are waiting for a memory grant

This query tells us which SQLs are waiting for memory space to be granted for processing their hash and sort data

SELECT
session_id
, dop as [degree_of_parallelism]
, request_time
, requested_memory_kb
, query_cost
, timeout_sec
, wait_order
, CASE is_next_candidate WHEN 1 THEN 'Next Candidate' ELSE 'Not the Next Candidate' END AS is_next_candidate
, wait_time_ms
, syst.text AS sql_text
FROM sys.dm_exec_query_memory_grants sysm
CROSS APPLY sys.dm_exec_sql_text (sysm.sql_handle) syst
WHERE wait_time_ms > 0
ORDER BY 1 DESC

Reference: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx?Redirected=true