sys.dm_exec_query_memory_grants
New: 14 April 2006
Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute. Queries that do not have to wait on a memory grant will not appear in this view.
| Column name | Data type | Description |
|---|---|---|
session_id |
smallint |
ID (SPID) of the session where this query is running. |
request_id |
int |
ID of the request. Unique in the context of the session. |
scheduler_id |
int |
ID of the scheduler that is scheduling this query. |
dop |
smallint |
Degree of parallelism of this query. |
request_time |
datetime |
Date and time when this query requested the memory grant. |
grant_time |
datetime |
Date and time when memory was granted for this query. NULL if memory is not granted yet. |
requested_memory_kb |
bigint |
Total requested amount of memory in kilobytes. |
granted_memory_kb |
bigint |
Total amount of memory actually granted in kilobytes. Can be NULL if the memory is not granted yet. For a typical situation, this value should be the same as requested_memory_kb. For index creation, the server may allow additional on-demand memory beyond initially granted memory. |
required_memory_kb |
bigint |
Minimum memory required to run this query in kilobytes. requested_memory_kb is the same or larger than this amount. |
used_memory_kb |
bigint |
Physical memory used at this moment in kilobytes. |
max_used_memory_kb |
bigint |
Maximum physical memory used up to this moment in kilobytes. |
query_cost |
float |
Estimated query cost. |
timeout_sec |
int |
Time-out in seconds before this query gives up the memory grant request. |
resource_semaphore_id |
smallint |
ID of the resource semaphore on which this query is waiting. |
queue_id |
smallint |
ID of waiting queue where this query waits for memory grants. NULL if the memory is already granted. |
wait_order |
int |
Sequential order of waiting queries within the specified queue_id. This value can change for a given query if other queries get memory grants or time out. NULL if memory is already granted. |
is_next_candidate |
bit |
Candidate for next memory grant. 1 = Yes 0 = No NULL = Memory is already granted. |
wait_time_ms |
bigint |
Wait time in milliseconds. NULL if the memory is already granted. |
plan_handle |
varbinary(64) |
Identifier for this query plan. Use sys.dm_exec_query_plan to extract the actual XML plan. |
sql_handle |
varbinary(64) |
Identifier for Transact-SQL text for this query. Use sys.dm_exec_sql_text to get the actual Transact-SQL text. |
Permissions
Requires VIEW SERVER STATE permission on the server.
Remarks
A typical debugging scenario for query time-out may look like the following:
- Check overall system memory status using sys.dm_os_memory_clerks, sys.dm_os_sys_info, and various performance counters.
- Check for query-execution memory reservations in sys.dm_os_memory_clerks where
type = 'MEMORYCLERK_SQLQERESERVATIONS'. - Check for queries waiting for grants using sys.dm_exec_query_memory_grants.
- Further examine memory-intensive queries using sys.dm_exec_requests.
- If a runaway query is suspected, examine the Showplan from sys.dm_exec_query_plan and batch text from sys.dm_exec_sql_text.
Queries that use dynamic management views that include ORDER BY or aggregates may increase memory consumption and thus contribute to the problem they are troubleshooting. sys.dm_exec_query_memory_grants is new in SQL Server 2005 Service Pack 1. Information in sys.dm_exec_query_memory_grants is specific to the internal implementation of SQL Server 2005 and is expected to change in the next release of SQL Server. Use sys.dm_exec_query_memory_grants for troubleshooting, but do not include it in applications that will use future versions of SQL Server.
See Also
Reference
sys.dm_exec_query_resource_semaphores
Execution Related Dynamic Management Views and Functions