Skip to content

Latest commit

 

History

History
140 lines (116 loc) · 12.6 KB

sys-dm-exec-query-memory-grants-transact-sql.md

File metadata and controls

140 lines (116 loc) · 12.6 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sys.dm_exec_query_memory_grants (Transact-SQL)
sys.dm_exec_query_memory_grants (Transact-SQL)
rwestMSFT
randolphwest
02/24/2023
sql
system-objects
reference
dm_exec_query_memory_grants_TSQL
sys.dm_exec_query_memory_grants
sys.dm_exec_query_memory_grants_TSQL
dm_exec_query_memory_grants
sys.dm_exec_query_memory_grants dynamic management view
TSQL
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||>=aps-pdw-2016||=azure-sqldw-latest

sys.dm_exec_query_memory_grants (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

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 [!INCLUDE ssazure-sqldb], 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.

Note

To call this from [!INCLUDEssazuresynapse-md] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_exec_query_memory_grants. [!INCLUDEsynapse-analytics-od-unsupported-syntax]

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 Non-unique ID of the resource semaphore on which this query is waiting.

Note: This ID is unique in versions of [!INCLUDEssNoVersion] that are earlier than [!INCLUDEsql2008-md]. This change can affect troubleshooting query execution. For more information, see the "Remarks" section later in this article.
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 [!INCLUDEtsql] text for this query. Use sys.dm_exec_sql_text to get the actual [!INCLUDEtsql] text.
group_id int ID for the workload group where this query is running.
pool_id int ID of the resource pool that this workload group belongs to.
is_small tinyint When 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_kb bigint Size, in kilobytes (KB), of the memory grant to fit everything into physical memory. This is based on the cardinality estimate.
pdw_node_id int The identifier for the node that this distribution is on.

Applies to: [!INCLUDEssazuresynapse-md], [!INCLUDEssPDW]
reserved_worker_count bigint Number of reserved worker threads.

Applies to: [!INCLUDEssNoVersion] (Starting with [!INCLUDEsssql16-md]) and [!INCLUDE ssazure-sqldb]
used_worker_count bigint Number of worker threads used at this moment.

Applies to: [!INCLUDEssNoVersion] (Starting with [!INCLUDEsssql16-md]) and [!INCLUDE ssazure-sqldb]
max_used_worker_count bigint Maximum number of worker threads used up to this moment.

Applies to: [!INCLUDEssNoVersion] (Starting with [!INCLUDEsssql16-md]) and [!INCLUDE ssazure-sqldb]
reserved_node_bitmap bigint Bitmap of NUMA nodes where worker threads are reserved.

Applies to: [!INCLUDEssNoVersion] (Starting with [!INCLUDEsssql16-md]) and [!INCLUDE ssazure-sqldb]

Permissions

On [!INCLUDEssNoVersion_md], requires VIEW SERVER STATE permission.
On [!INCLUDE ssazure-sqldb], requires the VIEW DATABASE STATE permission in the database.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Remarks

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 [!INCLUDEsql2008-md], each pool behaves like a small independent server instance and requires two 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 [!INCLUDEssVersion2005].

Examples

A typical debugging scenario for query time-out may investigate 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 waiting1 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;

    1 In this scenario, the wait type is typically RESOURCE_SEMAPHORE. For more information, see sys.dm_os_wait_stats (Transact-SQL).

  • Search cache for queries with memory grants using sys.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

  • If a runaway query is suspected, examine the Showplan in the query_plan column from sys.dm_exec_query_plan and query batch text from sys.dm_exec_sql_text. Further examine memory-intensive queries currently executing, using sys.dm_exec_requests.

    --Active requests with memory grants
SELECT
--Session data 
  s.[session_id], s.open_transaction_count
--Memory usage
, r.granted_query_memory, mg.grant_time, mg.requested_memory_kb, mg.granted_memory_kb, mg.required_memory_kb, mg.used_memory_kb, mg.max_used_memory_kb     
--Query 
, query_text = t.text, input_buffer = ib.event_info, query_plan_xml = qp.query_plan, request_row_count = r.row_count, session_row_count = s.row_count
--Session history and status
, s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads, session_status = s.[status], request_status = r.status
--Session connection information
, s.host_name, s.program_name, s.login_name, s.client_interface_name, s.is_user_process
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests AS r 
    ON r.[session_id] = s.[session_id]
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg 
    ON mg.[session_id] = s.[session_id]
OUTER APPLY sys.dm_exec_sql_text (r.[sql_handle]) AS t
OUTER APPLY sys.dm_exec_input_buffer(s.[session_id], NULL) AS ib 
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp 
WHERE mg.granted_memory_kb > 0
ORDER BY mg.granted_memory_kb desc, mg.requested_memory_kb desc;
GO

See also