Export (0) Print
Expand All

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.

Requires VIEW SERVER STATE permission on the server.

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft