sys.dm_exec_query_memory_grants

2008-12-03

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.

Dela via

sys.dm_exec_query_memory_grants

Permissions

Remarks

See Also

Reference

Help and Information

Ytterligare resurser