sys.dm_exec_query_memory_grants (Transact-SQL)

 

Updated: February 1, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns information about all queries that have requested and are waiting for a memory grant or have been given a memory grant. Queries that do not require a memory grant will not appear in this view. For example, sort and hash join operations have memory grants for query execution, while queries without an ORDER BY clause will not have a memory grant.

In Azure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesn’t belong to the connected tenant is filtered out. In addition, the values in the columns scheduler_id, wait_order, pool_id, group_id are filtered; the column value is set to NULL.

System_CAPS_ICON_note.jpg Note


To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_exec_query_memory_grants.

Column nameData typeDescription
session_idsmallintID (SPID) of the session where this query is running.
request_idintID of the request. Unique in the context of the session.
scheduler_idintID of the scheduler that is scheduling this query.
dopsmallintDegree of parallelism of this query.
request_timedatetimeDate and time when this query requested the memory grant.
grant_timedatetimeDate and time when memory was granted for this query. NULL if memory is not granted yet.
requested_memory_kbbigintTotal requested amount of memory in kilobytes.
granted_memory_kbbigintTotal 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_kbbigintMinimum memory required to run this query in kilobytes. requested_memory_kb is the same or larger than this amount.
used_memory_kbbigintPhysical memory used at this moment in kilobytes.
max_used_memory_kbbigintMaximum physical memory used up to this moment in kilobytes.
query_costfloatEstimated query cost.
timeout_secintTime-out in seconds before this query gives up the memory grant request.
resource_semaphore_idsmallintNon-unique ID of the resource semaphore on which this query is waiting.

 Note: This ID is unique in versions of SQL Server that are earlier than SQL Server 2008. This change can affect troubleshooting query execution. For more information, see the "Remarks" section later in this topic.
queue_idsmallintID of waiting queue where this query waits for memory grants. NULL if the memory is already granted.
wait_orderintSequential 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_candidatebitCandidate for next memory grant.

1 = Yes

0 = No

NULL = Memory is already granted.
wait_time_msbigintWait time in milliseconds. NULL if the memory is already granted.
plan_handlevarbinary(64)Identifier for this query plan. Use sys.dm_exec_query_plan to extract the actual XML plan.
sql_handlevarbinary(64)Identifier for Transact-SQL text for this query. Use sys.dm_exec_sql_text to get the actual Transact-SQL text.
group_idintID for the workload group where this query is running.
pool_idintID of the resource pool that this workload group belongs to.
is_smalltinyintWhen set to 1, indicates that this grant uses the small resource semaphore. When set to 0, indicates that a regular semaphore is used.
ideal_memory_kbbigintSize, in kilobytes (KB), of the memory grant to fit everything into physical memory. This is based on the cardinality estimate.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

Requires VIEW SERVER STATE permission on the server.

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.

    --Find all queries waiting in the memory queue  
    SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null  
    
    
  • Search cache for queries with memory grants usingsys.dm_exec_cached_plans (Transact-SQL) and sys.dm_exec_query_plan (Transact-SQL)

    -- retrieve every query plan from the plan cache  
    USE master;  
    GO  
    SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
    GO  
    
    
    
  • Further examine memory-intensive queries using sys.dm_exec_requests.

    --Find top 5 queries by average CPU time  
    SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
    Plan_handle, query_plan   
    FROM sys.dm_exec_query_stats AS qs  
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)  
    ORDER BY total_worker_time/execution_count DESC;  
    GO  
    
    
    
  • 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.

The Resource Governor feature enables a database administrator to distribute server resources among resource pools, up to a maximum of 64 pools. Beginning with SQL Server 2008, each pool behaves like a small independent server instance and requires 2 semaphores. The number of rows that are returned from sys.dm_exec_query_resource_semaphores can be up to 20 times more than the rows that are returned in SQL Server 2005.

sys.dm_exec_query_resource_semaphores (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)

Community Additions

ADD
Show: