sys.dm_exec_query_memory_grants (Transact-SQL)

适用于:SQL Server (所有受支持的版本) Azure SQL数据库Azure Synapse Analytics Analytics Platform System (PDW)

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

在Azure SQL数据库中,动态管理视图无法公开影响数据库包含或公开用户有权访问的其他数据库的信息。 为了避免公开此信息,将筛选出包含不属于已连接租户的数据的每一行。此外,将筛选列中scheduler_idwait_orderpool_idgroup_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。 For a typical situation, this value should be the same as 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 在早于 2008 SQL Server 的 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 2016 (13.x) ) 开始和Azure SQL数据库
used_worker_count bigint 此时使用 的工作线程 数。

适用于:SQL Server (从 2016 SQL Server 2016 (13.x) ) 开始和Azure SQL数据库
max_used_worker_count bigint 最多使用此时刻 的工作线程 数。

适用于:SQL Server (从 2016 SQL Server 2016 (13.x) ) 开始和Azure SQL数据库
reserved_node_bitmap bigint 保留 工作线程 的 NUMA 节点的位图。

适用于:SQL Server (从 2016 SQL Server 2016 (13.x) ) 开始和Azure SQL数据库

权限

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

备注

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

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

示例

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

  • 使用 sys.dm_os_memory_clerkssys.dm_os_sys_info 和各种性能计数器检查总体系统内存状态。

  • 在何处type = 'MEMORYCLERK_SQLQERESERVATIONS'检查查询执行内存预留sys.dm_os_memory_clerks

  • 检查等待1 个查询是否使用 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在此方案中,等待类型通常是 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检查列中的 query_plan Showplan,并从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
    

另请参阅