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:

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

Help and Information

Getting SQL Server 2005 Assistance