sys.dm_exec_query_memory_grants (Transact-SQL)

项目
05/23/2023

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)

返回有关已请求且正在等待内存授予或已获得内存授予的所有查询的信息。不需要内存授予的查询将不会在此视图中显示。例如，排序和哈希联接操作具有用于查询执行的内存授予，而没有 ORDER BY 子句的查询将不具有内存授予。

在 Azure SQL 数据库中，动态管理视图不能公开会影响数据库包含的信息，也不能公开有关用户有权访问的其他数据库的信息。为了避免公开此信息，将筛选出包含不属于已连接租户的数据的每一行。此外，将筛选列 scheduler_id、 wait_order、 pool_id中的 group_id 值;列值设置为 NULL。

注意

若要从 Azure Synapse Analytics 或 Analytics Platform System (PDW) 调用此名称，请使用名称 sys.dm_pdw_nodes_exec_query_memory_grants。 Azure Synapse Analytics 中的无服务器 SQL 池不支持此语法。

列名	Data type	描述
session_id	smallint	正在运行查询的会话 ID (SPID)。
request_id	int	请求的 ID。在会话的上下文中是唯一的。
scheduler_id	int	正在计划查询的计划程序的 ID。
Dop	smallint	查询的并行度。
request_time	datetime	查询请求内存授予的日期和时间。
grant_time	datetime	向查询授予内存的日期和时间。如果尚未授予内存，则此值为 NULL。
requested_memory_kb	bigint	请求的内存总量 (KB)。
granted_memory_kb	bigint	实际授予的内存总量 (KB)。如果尚未授予内存，该值可以为 NULL。对于典型情况，此值应与 `requested_memory_kb`相同。创建索引时，除了初始授予的内存外，服务器还允许增加按需分配的内存。
required_memory_kb	bigint	运行查询所需的最小内存 (KB)。 `requested_memory_kb` 等于或大于此量。
used_memory_kb	bigint	此刻使用的物理内存 (KB)。
max_used_memory_kb	bigint	到此刻为止所用的最大物理内存 (KB)。
query_cost	float	估计查询开销。
timeout_sec	int	查询放弃内存授予请求前的超时时间（秒）。
resource_semaphore_id	smallint	此查询正在等待的资源信号量的非唯一 ID。注意：此 ID 在早于 SQL Server 2008 (10.0.x) 的 SQL Server 版本中是唯一的。此更改会对故障排除查询执行造成影响。有关详细信息，请参阅本文后面的“备注”部分。
queue_id	smallint	查询等待内存授予时所在等待队列的 ID。如果已授予内存，则为 NULL。
wait_order	int	指定 `queue_id`内等待查询的顺序。如果其他查询获得内存授予或超时，则给定查询的此值可能会更改。如果已授予内存，则为 NULL。
is_next_candidate	bit	下一个内存授予的候选对象。 1 = 是 0 = 否 NULL = 已授予内存。
wait_time_ms	bigint	等待时间（毫秒）。如果已授予内存，则为 NULL。
plan_handle	varbinary(64)	查询计划的标识符。使用 `sys.dm_exec_query_plan` 提取实际的 XML 计划。
sql_handle	varbinary(64)	此查询的 Transact-SQL 文本的标识符。使用 `sys.dm_exec_sql_text` 获取实际的 Transact-SQL 文本。
group_id	int	在其中运行此查询的工作负荷组的 ID。
pool_id	int	该工作负荷组所属的资源池的 ID。
is_small	tinyint	如果设置为 1，则指示此授予使用小型资源信号量。如果设置为 0，则指示使用常规信号量。
ideal_memory_kb	bigint	将所有内容存放在物理内存中所需的内存授予的大小（以 KB 为单位）。这基于基数估计。
pdw_node_id	int	此分发所在节点的标识符。适用于：Azure Synapse Analytics、Analytics Platform System (PDW)
reserved_worker_count	bigint	保留的工作线程数。适用于：SQL Server (从 2016 SQL Server 开始 (13.x) ) 和 Azure SQL 数据库
used_worker_count	bigint	此时使用的工作线程数。适用于：SQL Server (从 2016 SQL Server 开始 (13.x) ) 和 Azure SQL 数据库
max_used_worker_count	bigint	截至此刻已用的最大工作线程数。适用于：SQL Server (从 2016 SQL Server 开始 (13.x) ) 和 Azure SQL 数据库
reserved_node_bitmap	bigint	保留工作线程的 NUMA 节点的位图。适用于：SQL Server (从 2016 SQL Server 开始 (13.x) ) 和 Azure SQL 数据库

权限

在 SQL Server 上，需要 VIEW SERVER STATE 权限。
在 Azure SQL 数据库上，需要在数据库中拥有 VIEW DATABASE STATE 权限。

SQL Server 2022 及更高版本的权限

需要对服务器具有 VIEW SERVER PERFORMANCE STATE 权限。

注解

使用包含 ORDER BY 或聚合的动态管理视图的查询可能会增加内存消耗，从而导致其正在排查的问题。

数据库管理员可以使用资源调控器功能在多个资源池之间分发服务器资源，最多可为 64 个池。从 2008 SQL Server (10.0.x) 开始，每个池的行为类似于一个小型独立服务器实例，并且需要两个信号灯。从 sys.dm_exec_query_resource_semaphores 返回的行数最多是SQL Server 2005 (9.x) 中返回的行数的 20 倍。

示例

查询超时的典型调试方案可能会调查以下内容：

使用 sys.dm_os_memory_clerks、sys.dm_os_sys_info 和各种性能计数器检查总体系统内存状态。
检查中的sys.dm_os_memory_clerkstype = 'MEMORYCLERK_SQLQERESERVATIONS'查询执行内存预留。
使用 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;
```
¹在此方案中，等待类型通常是 RESOURCE_SEMAPHORE。有关详细信息，请参阅 sys.dm_os_wait_stats (Transact-SQL)。

使用 sys.dm_exec_cached_plans (Transact-SQL) 和 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

如果怀疑查询失控，请检查列中sys.dm_exec_query_plan中的 Showplanquery_plan，并从sys.dm_exec_sql_text查询批处理text。使用 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

sys.dm_exec_query_memory_grants (Transact-SQL)

权限

SQL Server 2022 及更高版本的权限

注解

示例

另请参阅

反馈

反馈

其他资源