<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="41232" RequiredMemory="5248" DesiredMemory="46016" RequestedMemory="46016" GrantWaitTime="0" GrantedMemory="46016" MaxUsedMemory="45816" MaxQueryMemory="277688" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution" />
此处需要解释几个术语。 查询可能需要一定数量的执行内存 (DesiredMemory) ，并且通常会请求 (RequestedMemory) 。 在运行时，SQL Server根据 GrantedMemory) 可用性 (授予请求的全部或部分内存。 最后，查询可能会使用或多或少的初始请求内存 (MaxUsedMemory) 。 如果查询优化器高估了所需的内存量，它将使用小于请求的大小。 但是，该内存被浪费了，因为它可能被另一个请求使用。 另一方面，如果优化器低估了所需的内存大小，则多余的行可能会溢出到磁盘，以在执行时完成工作。 SQL Server将额外的行推送到磁盘，并将其用作临时工作区，而不是分配比最初请求的大小更多的内存。 有关详细信息，请参阅 内存授予注意事项中的工作文件和工作表。
让我们回顾一下你可能遇到的有关此内存使用者的不同术语。 同样，所有这些概念都描述了与相同的内存分配相关的概念。
查询执行内存 (QE 内存) ： 此术语用于突出显示在执行查询期间使用排序或哈希内存的事实。 通常，QE 内存是查询生命周期中内存的最大消耗者。
查询执行 (QE) 预留或内存预留： 当查询需要内存进行排序或哈希操作时，它会发出内存预留请求。 该预留请求是在编译时根据估计基数计算的。 稍后执行查询时，SQL Server部分或完全授予该请求，具体取决于内存可用性。 最后，查询可能会使用已授予内存的百分比。 有一个名为“MEMORYCLERK_SQLQERESERVATIONS”的内存) 的内存职员 (会计， (检查 DBCC MEMORYSTATUS 或sys.dm_os_memory_clerks) 跟踪这些内存分配。
内存授予：当SQL Server向执行查询授予请求的内存时，表示发生了内存授予。 有一些性能计数器使用术语“授予”。这些计数器 Memory Grants Outstanding 和 Memory Grants Pending显示满足或等待的内存授予计数。 它们不考虑内存授予大小。 例如，仅一个查询就可能消耗 4 GB 内存来执行排序，但这不会反映在这两个计数器中的任何一个中。
工作区内存 是另一个描述相同内存的术语。 通常，你可能会在 Perfmon 计数器 Granted Workspace Memory (KB)中看到此术语，它反映了当前用于排序、哈希、大容量复制和索引创建操作的内存总量（以 KB 为单位）。 Maximum Workspace Memory (KB)另一个计数器占可用于任何请求的最大工作区内存量，这些请求可能需要执行此类哈希、排序、大容量复制和索引创建操作。 这两个计数器之外很少遇到术语“工作区内存”。
在大多数情况下，当线程请求SQL Server中的内存以完成某些操作，并且内存不可用时，请求失败并出现内存不足错误。 但是，在几种异常情况下，线程不会失败，而是等待内存可用。 其中一种方案是内存授予，另一种方案是查询编译内存。 SQL Server使用称为信号灯的线程同步对象来跟踪为查询执行授予了多少内存。 如果SQL Server用完预定义的 QE 工作区，则会导致查询等待，而不是导致查询失败并出现内存不足错误。 鉴于允许工作区内存占用总体SQL Server内存的很大一部分，因此在此空间中等待内存会严重影响性能。 大量并发查询请求了执行内存，它们共同耗尽了 QE 内存池，或者一些并发查询请求了非常大的授权。 无论哪种方式，生成的性能问题都可能出现以下症状：
缓冲区缓存中的数据页和索引页可能已刷新，以便为大型内存授予请求提供空间。 这意味着，必须满足来自查询请求的页面读取， () 明显慢的操作。
其他内存分配请求可能会失败并出现内存不足错误，因为资源与排序、哈希或索引生成操作绑定。
需要执行内存的请求正在等待资源变为可用，并且需要很长时间才能完成。 换句话说，对最终用户来说，这些查询速度很慢。
因此，如果在 Perfmon 中观察到查询执行内存等待，动态管理视图 (DMV) 或 DBCC MEMORYSTATUS，则必须采取措施来解决此问题，尤其是在问题频繁出现时。 有关详细信息，请参阅 开发人员可对排序和哈希操作执行哪些操作。
如何识别等待查询执行内存
有多种方法可以确定 QE 预留的等待时间。 选择最适合你查看服务器级别的大图。 其中一些工具可能不可用， (例如，Perfmon 在 Azure SQL Database) 中不可用。 确定问题后，必须在单个查询级别向下钻取，以查看哪些查询需要优化或重写。
在服务器级别，使用以下方法：
资源信号灯 DMV sys.dm_exec_query_resource_semaphores 有关详细信息，请参阅 sys.dm_exec_query_resource_semaphores。
性能监视器计数器 有关详细信息，请参阅SQL Server内存管理器对象。
DBCC MEMORYSTATUS 有关详细信息，请参阅 DBCC MEMORYSTATUS。
内存管理员 DMV sys.dm_os_memory_clerks 有关详细信息，请参阅 sys.dm_os_memory_clerks。
使用扩展事件 (XEvents) 识别内存授予 有关详细信息，请参阅 XEvents) (扩展事件 。
在单个查询级别，使用以下方法：
使用sys.dm_exec_query_memory_grants标识特定查询：当前正在执行的查询。 有关详细信息，请参阅 sys.dm_exec_query_memory_grants。
使用sys.dm_exec_requests标识特定查询：当前正在执行的查询。 有关详细信息，请参阅 sys.dm_exec_requests。
使用sys.dm_exec_query_stats标识特定查询：查询的历史统计信息。 有关详细信息，请参阅 sys.dm_exec_query_stats。
使用 查询存储 (QDS) 识别特定查询，sys.query_store_runtime_stats：使用 QDS 查询的历史统计信息。 有关详细信息，请参阅 sys.query_store_runtime_stats。
聚合内存使用情况统计信息
资源信号灯 DMV sys.dm_exec_query_resource_semaphores
此 DMV 按资源池 (内部、默认和用户创建的) 细分查询预留内存， (resource_semaphore 常规查询请求和小型查询请求) 。 有用的查询可能是：
SELECT
pool_id
,total_memory_kb
,available_memory_kb
,granted_memory_kb
,used_memory_kb
,grantee_count, waiter_count
,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs
以下示例输出显示，22 个请求使用了大约 900 MB 的查询执行内存，还有 3 个请求正在等待。 这发生在默认池 (pool_id = 2) ，常规查询信号量 (resource_semaphore_id = 0) 。
pool_id total_memory_kb available_memory_kb granted_memory_kb used_memory_kb grantee_count waiter_count resource_semaphore_id
------- --------------- ------------------- ----------------- -------------- ------------- ------------ ---------------------
1 30880 30880 0 0 0 0 0
1 5120 5120 0 0 0 0 1
2 907104 0 907104 898656 22 3 0
2 40960 40960 0 0 0 0 1
(4 rows affected)
类似信息可通过性能监视器计数器获取，可在其中观察当前授予的请求 () Memory Grants Outstanding 、等待授予请求 (Memory Grants Pending) ，以及内存授予使用的内存量 (Granted Workspace Memory (KB)) 。 在下图中，未完成的授权为 18，挂起的授予为 2，授予的工作区内存为 828，288 KB。 Memory Grants Pending具有非零值的 Perfmon 计数器指示内存已耗尽。
有关详细信息，请参阅 SQL Server内存管理器对象。
SQLServer、内存管理器：最大工作区内存 (KB)
SQLServer、内存管理器：未完成内存授予
SQLServer、内存管理器：内存授予挂起
SQLServer、内存管理器：授予工作区内存 (KB)
DBCC MEMORYSTATUS
另一个可以查看查询预留内存的详细信息的位置是 DBCC MEMORYSTATUS (查询内存对象部分) 。 可以查看用户查询的 Query Memory Objects (default) 输出。 例如，如果使用名为 PoolAdmin 的资源池启用了Resource Governor，则可以同时查看 Query Memory Objects (default) 和 Query Memory Objects (PoolAdmin)。
下面是系统的示例输出，其中 18 个请求已获得查询执行内存，2 个请求正在等待内存。 可用计数器为零，表示不再有可用的工作区内存。 这一事实解释了两个等待的请求。 显示 Wait Time 自请求放入等待队列以来的运行时间（以毫秒为单位）。 有关这些计数器的详细信息，请参阅 查询内存对象。
Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 18
Waiting 2
Available 0
Current Max 103536
Future Max 97527
Physical Max 139137
Next Request 5752
Waiting For 8628
Cost 16
Timeout 401
Wait Time 2750
(11 rows affected)
Small Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 0
Waiting 0
Available 5133
Current Max 5133
Future Max 5133
DBCC MEMORYSTATUS 还显示有关跟踪查询执行内存的内存管理器的信息。 以下输出显示，为查询执行分配的页面 (QE) 预留超过 800 MB。
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
------------------------------------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 824640
内存管理员 DMV sys.dm_os_memory_clerks
如果需要更多表格结果集（不同于基于 DBCC MEMORYSTATUS节的 ），则可以将 sys.dm_os_memory_clerks 用于类似信息。 MEMORYCLERK_SQLQERESERVATIONS查找内存文员。 但是，查询内存对象在此 DMV 中不可用。
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
下面是示例输出：
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
使用扩展事件 (XEvents) 识别内存授予
有多个扩展事件提供内存授予信息，并使你能够通过跟踪捕获此信息：
sqlserver.additional_memory_grant：当查询尝试在执行期间获取更多内存授予时发生。 未能获取此额外的内存授予可能会导致查询速度变慢。
sqlserver.query_memory_grant_blocking：当查询在等待内存授予时阻止其他查询时发生。
sqlserver.query_memory_grant_info_sampling：在随机采样查询的末尾发生，提供内存授予信息 (可用于遥测) 。
sqlserver.query_memory_grant_resource_semaphores：每个资源调控器资源池每隔五分钟发生一次。
sqlserver.query_memory_grant_usage：对于内存授予超过 5 MB 的查询，在查询处理结束时发生，以告知用户内存授予不准确之处。
sqlserver.query_memory_grants：每个具有内存授予的查询每隔五分钟发生一次。
内存授予反馈扩展事件
有关查询处理内存授予反馈功能的信息，请参阅 内存授予反馈。
sqlserver.memory_grant_feedback_loop_disabled：在禁用内存授予反馈循环时发生。
sqlserver.memory_grant_updated_by_feedback：通过反馈更新内存授予时发生。
与内存授予相关的查询执行警告
sqlserver.execution_warning：当 T-SQL 语句或存储过程等待内存授予超过一秒或首次尝试获取内存失败时发生。 将此事件与确定等待事件的事件结合使用，以排查影响性能的争用问题。
sqlserver.hash_spill_details：如果内存不足，无法处理哈希联接的生成输入，则发生在哈希处理结束时。 将此事件与任何 query_pre_execution_showplan 或 query_post_execution_showplan 事件一起使用，以确定生成的计划中哪个操作导致哈希溢出。
sqlserver.hash_warning：当内存不足，无法处理哈希联接的生成输入时发生。 这会导致在对生成输入进行分区时出现哈希递归，或者在生成输入的分区超过最大递归级别时产生哈希救助。 将此事件与任何 query_pre_execution_showplan 或 query_post_execution_showplan 事件一起使用，以确定生成的计划中导致哈希警告的操作。
sqlserver.sort_warning：当正在执行的查询的排序操作不适合内存时发生。 此事件不会为索引创建导致的排序操作生成，而仅适用于查询中的排序操作。 (例如，语句Select中的 .Order By) 使用此事件来标识由于排序操作而执行缓慢的查询，尤其是当 = 2 时warning_type，指示需要多次传递数据才能排序。
默认情况下，生成扩展事件的以下查询计划包含 granted_memory_kb 和 ideal_memory_kb 字段：
sqlserver.query_plan_profile
sqlserver.query_post_execution_plan_profile
sqlserver.query_post_execution_showplan
sqlserver.query_pre_execution_showplan
列存储索引生成
XEvents 涵盖的一个领域是在生成列存储期间使用的执行内存。 下面是可用的事件列表：
sqlserver.column_store_index_build_low_memory：存储引擎检测到内存不足，并且行组大小已减小。 这里有几个感兴趣的列。
sqlserver.column_store_index_build_memory_trace：跟踪索引生成期间的内存使用情况。
sqlserver.column_store_index_build_memory_usage_scale_down：存储引擎已缩减。
sqlserver.column_store_index_memory_estimation：显示 COLUMNSTORE 行组生成期间的内存估计结果。
标识特定查询
查看单个请求级别时，可能会发现两种类型的查询。 消耗大量查询执行内存的查询和等待相同内存的查询。 后一组可能包含对内存授予需求不大的请求，如果是，可以将注意力集中在其他位置。 但是，如果他们请求巨大的内存大小，他们也可能是罪魁祸首。 如果你发现是这种情况，请专注于它们。 通常，发现一个特定的查询是罪犯，但许多实例是生成的。 获取内存授予的实例会导致同一查询的其他实例等待授予。 无论具体情况如何，最终都必须确定查询和请求的执行内存的大小。
使用sys.dm_exec_query_memory_grants识别特定查询
若要查看单个请求及其请求和已授予的内存大小，可以查询 sys.dm_exec_query_memory_grants 动态管理视图。 此 DMV 显示有关当前正在执行的查询的信息，而不是历史信息。
以下语句从 DMV 获取数据，并提取查询文本和查询计划作为结果：
SELECT
session_id
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
,queue_id
,wait_order
,wait_time_ms
,is_next_candidate
,pool_id
,text
,query_plan
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
下面是活动 QE 内存消耗期间查询的缩写示例输出。 大多数查询都授予了内存，如 granted_memory_kb 所示，并且 used_memory_kb 是非 NULL 数值。 未获得请求授权的查询正在等待执行内存和 granted_memory_kb = NULL。 此外，它们被置于一个等待队列中，其值为 queue_id 6。 它们 wait_time_ms 表示等待时间大约为 37 秒。 会话 72 是下一个行以获取授权，如 = 1 指示 wait_order ，而会话 74 位于其后，其后为 wait_order = 2。
session_id requested_memory_kb granted_memory_kb used_memory_kb queue_id wait_order wait_time_ms is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80 41232 41232 40848 NULL NULL NULL NULL 2
83 41232 41232 40848 NULL NULL NULL NULL 2
84 41232 41232 40848 NULL NULL NULL NULL 2
74 41232 NULL NULL 6 2 37438 0 2
78 41232 41232 40848 NULL NULL NULL NULL 2
81 41232 41232 40848 NULL NULL NULL NULL 2
71 41232 41232 40848 NULL NULL NULL NULL 2
75 41232 NULL NULL 6 0 37438 1 2
82 41232 41232 40848 NULL NULL NULL NULL 2
76 41232 41232 40848 NULL NULL NULL NULL 2
79 41232 41232 40848 NULL NULL NULL NULL 2
85 41232 41232 40848 NULL NULL NULL NULL 2
70 41232 41232 40848 NULL NULL NULL NULL 2
55 41232 41232 40848 NULL NULL NULL NULL 2
59 41232 NULL NULL 6 3 37438 0 2
62 41232 41232 40848 NULL NULL NULL NULL 2
54 41232 41232 40848 NULL NULL NULL NULL 2
77 41232 41232 40848 NULL NULL NULL NULL 2
52 41232 41232 40848 NULL NULL NULL NULL 2
72 41232 NULL NULL 6 1 37438 0 2
69 41232 41232 40848 NULL NULL NULL NULL 2
73 41232 41232 40848 NULL NULL NULL NULL 2
66 41232 NULL NULL 6 4 37438 0 2
68 41232 41232 40848 NULL NULL NULL NULL 2
63 41232 41232 40848 NULL NULL NULL NULL 2
使用sys.dm_exec_requests标识特定查询
SQL Server中有一个等待类型，指示查询正在等待内存授予 RESOURCE_SEMAPHORE。 对于单个请求，你可能会看到此等待类型 sys.dm_exec_requests 。 后一个 DMV 是确定哪些查询是授予内存不足的受害者的最佳起点。 还可以将RESOURCE_SEMAPHOREsys.dm_os_wait_stats中的等待作为SQL Server级别的聚合数据点进行观察。 当由于其他并发查询已用完内存而无法授予查询内存请求时，将显示此等待类型。 等待请求计数高和等待时间长表示使用执行内存或较大的内存请求大小的并发查询过多。
内存授予的等待时间有限。 例如，在 (过度等待后，超过 20 分钟) ，SQL Server查询超时，并引发错误 8645，“等待内存资源执行查询时发生超时。 重新运行查询。”在 中sys.dm_exec_query_memory_grants查看，可能会看到在服务器级别设置的timeout_sec超时值。 超时值可能因SQL Server版本略有不同。
使用 sys.dm_exec_requests，可以查看哪些查询已被授予内存以及该授予的大小。 此外，还可以通过查找等待类型来确定当前正在等待内存授予的 RESOURCE_SEMAPHORE 查询。 下面是一个查询，其中显示了授予的请求和等待的请求：
SELECT session_id, wait_type, wait_time, granted_query_memory, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE granted_query_memory > 0
OR wait_type = 'RESOURCE_SEMAPHORE'
示例输出显示已向两个请求授予内存，另有 20 个请求正在等待授予。 列 granted_query_memory 报告大小（以 8 KB 为单位）。 例如，值 34，709 表示授予的内存为 34，709 * 8 KB = 277，672 KB。
session_id wait_type wait_time granted_query_memory text
---------- ----------------------------------- -------------------- -------------------------------------------------------------------
65 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
66 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
67 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
68 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
69 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
70 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
71 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
72 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
73 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
74 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
75 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
76 ASYNC_NETWORK_IO 11 34709 select * from sys.messages order by message_id option (maxdop 1)
77 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
78 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
79 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
80 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
81 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
82 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
83 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
84 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
85 ASYNC_NETWORK_IO 14 34709 select * from sys.messages order by message_id option (maxdop 1)
86 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
87 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
88 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
89 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
使用sys.dm_exec_query_stats标识特定查询
如果此时未发生内存授予问题，但你想要识别有问题的查询，可以通过 查看历史查询数据 sys.dm_exec_query_stats。 数据的生存期与每个查询的查询计划相关联。 从计划缓存中删除计划时，将从此视图中删除相应的行。 换句话说，DMV 将统计信息保留在内存中，这些统计信息在SQL Server重启后或内存压力导致计划缓存释放后保留。 话又说，你可以在此处找到有价值的信息，尤其是对于聚合查询统计信息。 最近可能有人报告从查询中看到了大量内存授予，但当你查看服务器工作负载时，你可能会发现问题已经消失。 在这种情况下， sys.dm_exec_query_stats 可以提供其他 DVM 无法提供的见解。 下面是一个示例查询，可帮助你查找消耗最大执行内存量的前 20 个语句。 此输出显示各个语句，即使它们的查询结构相同。 例如， SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 是一个单独的行 SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 (只有筛选器谓词值) 变化。 查询获取最大授予大小大于 5 MB 的前 20 个语句。
SELECT TOP 20
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
,CONVERT(DECIMAL (10,2), max_grant_kb /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), min_grant_kb /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), (total_grant_kb / execution_count) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), max_used_grant_kb /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), min_used_grant_kb /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_used_grant_kb/ execution_count) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ execution_count) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ 1024.0)) AS total_grant_for_all_executions_mb
,execution_count
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE max_grant_kb > 5120 -- greater than 5 MB
ORDER BY max_grant_kb DESC
通过查看 聚合 query_hash的查询，可以获得更强大的见解。 此示例说明如何查找查询语句自首次缓存查询计划以来的所有实例的平均、最大和最小授予大小。
SELECT TOP 20
MAX(SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1)) AS sample_statement_text
,CONVERT(DECIMAL (10,2), SUM(max_grant_kb) /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), SUM(min_grant_kb) /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (SUM(total_grant_kb) / SUM(execution_count)) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), SUM(max_used_grant_kb) /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), SUM(min_used_grant_kb) /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_used_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_ideal_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), SUM(total_grant_kb) /1024.0) AS total_grant_all_executions_mb
,SUM(execution_count) AS execution_count
,query_hash
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
GROUP BY query_hash
HAVING SUM(max_grant_kb) > 5120 -- greater than 5 MB
ORDER BY SUM(max_grant_kb) DESC
OPTION (MAX_GRANT_PERCENT = 5)
列 Sample_Statement_Text 显示与查询哈希匹配的查询结构示例，但应不考虑语句中的特定值来读取该结构。 例如，如果语句包含 WHERE Id = 5，则可以以更通用的形式读取它： WHERE Id = @any_value。
下面是查询的缩写示例输出，其中仅显示了所选列：
sample_statement_text max_grant_mb avg_grant_mb max_grant_used_mb avg_grant_used_mb avg_ideal_grant_mb total_grant_all_executions_mb execution_count
----------------------------------------- ------------ ------------- ----------------- ----------------- ------------------ ----------------------------- ----------------
select de.ObjectName,de.CounterName,d 282.45 282.45 6.50 6.50 282.45 282.45 1
SELECT SCHEMA_NAME(udf.schema_id) AS [Sch 33.86 8.55 7.80 1.97 8.55 42.74 5
insert into #tmpCounterDateTime (CounterD 32.45 32.45 3.11 3.11 32.45 32.45 1
select db_id() dbid, db_name() dbname, * 20.80 1.30 5.75 0.36 1.30 20.80 16
SELECT SCHEMA_NAME(obj.schema_id) AS [Sch 20.55 5.19 5.13 1.28 5.19 25.93 5
SELECT xmlplan FROM (SELECT ROW_NUMBER() 19.69 1.97 1.09 0.11 1.97 19.69 10
if ( select max(cast(countervalue as floa 16.39 8.20 0.77 0.38 8.20 16.39 2
SELECT udf.name AS [Name], udf.object_id 11.36 5.08 1.66 0.83 5.08 20.33 4
select --* Database_I 10.94 5.47 1.98 0.99 5.47 10.94 2
IF (select max(cast(dat.countervalue as f 8.00 1.00 0.00 0.00 0.53 8.00 8
insert into #tmpCounterDateTime (CounterD 5.72 2.86 1.98 0.99 2.86 5.72 2
INSERT INTO #tmp (CounterDateTime, Counte 5.39 1.08 1.64 0.33 1.08 6.47 6
使用 查询存储 (QDS) 和 sys.query_store_runtime_stats 识别特定查询
如果已启用查询存储，则可以利用其持久化的历史统计信息。 与 来自 sys.dm_exec_query_stats的数据相反，这些统计信息在重启或内存压力SQL Server幸存下来，因为它们存储在数据库中。 QDS 还具有大小限制和保留策略。 有关详细信息，请参阅管理查询存储的最佳做法中的设置最佳查询存储捕获模式和在查询存储中保留最相关的数据部分。
使用此查询确定数据库是否查询存储启用：
SELECT name, is_query_store_on
FROM sys.databases
WHERE is_query_store_on = 1
在要调查的特定数据库的上下文中运行以下诊断查询：
SELECT
MAX(qtxt.query_sql_text) AS sample_sql_text
,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb
,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb
,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb
,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb
,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb
,SUM(count_executions) AS count_query_executions
FROM sys.query_store_runtime_stats rts
JOIN sys.query_store_plan p
ON p.plan_id = rts.plan_id
JOIN sys.query_store_query q
ON p.query_id = q.query_id
LEFT OUTER JOIN sys.query_store_query_text qtxt
ON q.query_text_id = qtxt.query_text_id
GROUP BY q.query_hash
HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB
ORDER BY SUM(avg_query_max_used_memory) DESC
OPTION (MAX_GRANT_PERCENT = 5)
此处的原则与 相同 sys.dm_exec_query_stats;你将看到语句的聚合统计信息。 但是，一个区别在于，对于 QDS，你只查看此数据库范围内的查询，而不是整个SQL Server。 因此，可能需要知道在其中执行特定内存授予请求的数据库。 否则，请在多个数据库中运行此诊断查询，直到找到可授予的内存。
下面是一个缩写的示例输出：
sample_sql_text avg_mem_grant_used_mb min_mem_grant_used_mb max_mem_grant_used_mb stdev_mem_grant_used_mb last_mem_grant_used_mb count_query_executions
----------------------------------------- ---------------------- ---------------------- ---------------------- ------------------------ ----------------------- ----------------------
SELECT qtxt.query_sql_text ,CONVERT(D 550.16 550.00 550.00 0.00 550.00 1
SELECT qtxt.query_sql_text ,rts.avg_q 61.00 36.00 65.00 10.87 51.00 14
SELECT qtxt.query_sql_text ,q.* ,rts 25.46 25.00 25.00 0.00 25.00 2
insert into #tmpStats select 5 'Database 13.69 13.00 13.00 0.03 13.00 16
SELECT q.* ,rts 11.93 11.00 12.00 0.23 12.00 2
SELECT * ,rts.avg_query_max_used_memory 9.70 9.00 9.00 0.00 9.00 1
SELECT qtxt.query_sql_text ,rts.avg_q 9.32 9.00 9.00 0.00 9.00 1
select db_id() dbid, db_name() dbname, * 7.33 7.00 7.00 0.00 7.00 9
SELECT q.* ,rts.avg_query_max_used_memo 6.65 6.00 6.00 0.00 6.00 1
(@_msparam_0 nvarchar(4000),@_msparam_1 5.17 4.00 5.00 0.68 4.00 2
自定义诊断查询
下面是一个查询，该查询合并了多个视图中的数据，包括前面列出的三个视图。 除了 提供的sys.dm_exec_query_resource_semaphores服务器级统计信息外，它还通过 sys.dm_exec_requests 和 sys.dm_exec_query_memory_grants提供会话及其授权的更全面视图。
此查询将为每个会话返回两行，因为 sys.dm_exec_query_resource_semaphores (一行用于常规资源信号灯，另一行用于小型查询资源信号灯) 。
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime
, r.session_id
, r.wait_time
, r.wait_type
, mg.request_time
, mg.grant_time
, mg.requested_memory_kb
/ 1024 requested_memory_mb
, mg.granted_memory_kb
/ 1024 AS granted_memory_mb
, mg.required_memory_kb
/ 1024 AS required_memory_mb
, max_used_memory_kb
/ 1024 AS max_used_memory_mb
, rs.pool_id as resource_pool_id
, mg.query_cost
, mg.timeout_sec
, mg.resource_semaphore_id
, mg.wait_time_ms AS memory_grant_wait_time_ms
, CASE mg.is_next_candidate
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Memory has been granted'
END AS 'Next Candidate for Memory Grant'
, r.command
, ltrim(rtrim(replace(replace (substring (q.text, 1, 1000), char(10), ' '), char(13), ' '))) [text]
, rs.target_memory_kb
/ 1024 AS server_target_grant_memory_mb
, rs.max_target_memory_kb
/ 1024 AS server_max_target_grant_memory_mb
, rs.total_memory_kb
/ 1024 AS server_total_resource_semaphore_memory_mb
, rs.available_memory_kb
/ 1024 AS server_available_memory_for_grants_mb
, rs.granted_memory_kb
/ 1024 AS server_total_granted_memory_mb
, rs.used_memory_kb
/ 1024 AS server_used_granted_memory_mb
, rs.grantee_count AS successful_grantee_count
, rs.waiter_count AS grant_waiters_count
, rs.timeout_error_count
, rs.forced_grant_count
, mg.dop
, r.blocking_session_id
, r.cpu_time
, r.total_elapsed_time
, r.reads
, r.writes
, r.logical_reads
, r.row_count
, s.login_time
, d.name
, s.login_name
, s.host_name
, s.nt_domain
, s.nt_user_name
, s.status
, c.client_net_address
, s.program_name
, s.client_interface_name
, s.last_request_start_time
, s.last_request_end_time
, c.connect_time
, c.last_read
, c.last_write
, qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_connections c
ON r.connection_id = c.connection_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
INNER JOIN sys.databases d
ON r.database_id = d.database_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle ) AS q
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
OPTION (MAXDOP 1, LOOP JOIN )
此 LOOP JOIN 诊断查询中使用提示以避免查询本身授予内存，并且不使用子 ORDER BY 句。 如果诊断查询最终等待授权本身，则诊断内存授予的目的将失效。 该 LOOP JOIN 提示可能会导致诊断查询变慢，但在这种情况下，获取诊断结果更为重要。
下面是此诊断查询中仅包含选定列的缩写示例输出。
session_id
wait_time
wait_type
requested_memory_mb
granted_memory_mb
required_memory_mb
max_used_memory_mb
resource_pool_id
示例输出清楚地说明了由 = 60 提交的 session_id 查询如何成功获得请求的 9 MB 内存授予，但成功启动查询执行只需要 7 MB。 最后，查询只使用了从服务器收到的 9 MB 中的 1 MB。 输出还显示会话 75 和 86 正在等待内存授予，因此 为 RESOURCE_SEMAPHOREwait_type。 他们的等待时间已超过 1，300 秒 (21 分钟) ，为 granted_memory_mbNULL。
此诊断查询是一个示例，因此可以根据需要随时对其进行修改。 Microsoft SQL Server支持使用的诊断工具中也使用了此查询的版本。
Microsoft SQL Server技术支持使用诊断工具来收集日志并更有效地排查问题。 SQL LogScout 和 Pssdiag Configuration Manager (SQLDiag) 收集前面所述的 DMV 的输出，以及可帮助诊断内存授予问题的性能监视器计数器。
如果使用 LightPerf、 GeneralPerf 或 DetailedPerf 方案运行 SQL LogScout，该工具将收集必要的日志。 然后，可以手动检查 YourServer_PerfStats.out 并查找 -- dm_exec_query_resource_semaphores -- 和 -- dm_exec_query_memory_grants -- 输出。 或者，可以使用 SQL Nexus 将来自 SQL LogScout 或 PSSDIAG 的输出导入到SQL Server数据库中，而不是手动检查。 SQL Nexus 创建两个表 tbl_dm_exec_query_resource_semaphores 和 tbl_dm_exec_query_memory_grants，其中包含诊断内存授予所需的信息。 SQL LogScout 和 PSSDIAG 还以 的形式收集 Perfmon 日志。BLG 文件，可用于查看性能监视器计数器部分所述的性能计数器。
为什么内存授予对开发人员或 DBA 很重要
根据 Microsoft 支持体验，内存授予问题往往是一些最常见的内存相关问题。 应用程序通常会执行看似简单的查询，这些查询最终可能会由于大量的排序或哈希操作而导致SQL Server性能问题。 此类查询不仅消耗大量SQL Server内存，还会导致其他查询等待内存可用，从而造成性能瓶颈。
使用此处概述的工具 (DMV、Perfmon 计数器和实际查询计划) ，可以确定哪些查询是大额授权使用者。 然后，可以优化或重写这些查询，以解决或减少工作区内存使用量。
开发人员可以对排序和哈希操作执行哪些操作
确定消耗大量查询预留内存的特定查询后，可以采取措施通过重新设计这些查询来减少内存授予。
导致查询中的排序和哈希操作的原因
第一步是了解查询中的哪些操作可能导致内存授予。
查询使用 SORT 运算符的原因：
ORDER BY (T-SQL) 会导致在流式处理为最终结果之前对行进行排序。
GROUP BY (T-SQL) 可能会在分组之前在查询计划中引入排序运算符，前提是不存在对分组列进行排序的基础索引。
DISTINCT (T-SQL) 的行为类似于 GROUP BY。 若要标识不同的行，请对中间结果进行排序，然后删除重复项。 如果由于有序索引查找或扫描而尚未对数据进行排序，则优化器将使用此 Sort 运算符之前。
查询优化器选择 合并联接 运算符时，要求对两个联接的输入进行排序。 如果其中一个表中的联接列上没有聚集索引，SQL Server可能会触发排序。
查询使用 HASH 查询计划运算符的原因：
此列表并不详尽，但包括哈希操作最常见的原因。 分析查询计划 以确定哈希匹配操作。
JOIN (T-SQL) ：联接表时，SQL Server可以选择三个物理运算符 Nested Loop、 Merge Join和 Hash Join。 如果SQL Server最终选择了哈希联接，则需要 QE 内存来存储和处理中间结果。 通常，缺少良好的索引可能会导致资源开销最大的联接运算符 Hash Join。 若要 检查查询计划 以标识 Hash Match，请参阅 逻辑和物理运算符参考。
DISTINCT (T-SQL) ： Hash Aggregate 运算符可用于消除行集中的重复项。 若要在查询计划中查找 Hash Match (Aggregate) ，请参阅 逻辑运算符和物理运算符参考。
UNION (T-SQL) ：这类似于 DISTINCT。 Hash Aggregate可用于删除此运算符的重复项。
SUM/AVG/MAX/MIN (T-SQL) ：任何聚合操作都可能作为 Hash Aggregate执行。 若要在查询计划中查找 Hash Match (Aggregate) ，请参阅 逻辑运算符和物理运算符参考。
了解这些常见原因有助于尽可能消除传入SQL Server的大型内存授予请求。
减少排序和哈希操作或授权大小的方法
使 统计信息 保持最新。 这一基本步骤可提高多个级别的查询的性能，确保在选择查询计划时查询优化器具有最准确的信息。 SQL Server根据统计信息确定为其内存授予请求的大小。 过期的统计信息可能会导致高估或低估授权请求，从而分别导致不必要地高授予请求或将结果溢出到磁盘。 确保已在数据库中启用 自动更新统计信息 和/或使用 UPDATE STATISTICS 或 sp_updatestats保持静态更新。
减少来自表的行数。 如果使用限制性更高的 WHERE 筛选器或 JOIN 并减少了行数，则查询计划中的后续排序将对较小的结果集进行排序或聚合。 较小的中间结果集需要较少的工作集内存。 这是开发人员可以遵循的一般规则，不仅可以节省工作集内存，还可以减少 CPU 和 I/O (此步骤并不总是) 。 如果编写良好且资源高效的查询已到位，则已满足此准则。
在联接列上创建索引以帮助合并联接。 查询计划中的中间操作受基础表上的索引的影响。 例如，如果表在联接列上没有索引，并且发现合并联接是最经济高效的联接运算符，则必须在执行联接之前对该表中的所有行进行排序。 如果列上存在索引，则可以消除排序操作。
创建索引以帮助避免哈希操作。 通常，基本查询优化首先检查查询是否具有适当的索引，以帮助他们减少读取，并在可能的情况下最小化或消除大型排序或哈希操作。 通常选择哈希联接来处理大型、未排序和未编制索引的输入。 创建索引可能会更改此优化器策略并加快数据检索速度。 有关创建索引的帮助，请参阅数据库引擎优化顾问和优化缺少索引建议的非聚集索引。
将 COLUMNSTORE 索引用于使用 GROUP BY的聚合查询。 处理非常大的行集并通常执行“分组依据”聚合的分析查询可能需要较大的内存区块才能完成工作。 如果没有提供有序结果的索引，查询计划中会自动引入排序。 一种非常大的结果可能会导致昂贵的内存授予。
如果不需要， ORDER BY 请将其删除。 如果结果以自己的方式将结果流式传输到应用程序，或者允许用户修改查看的数据的顺序，则无需在SQL Server端执行排序。 只需按照服务器生成数据的顺序将数据流式传输到应用程序，然后让最终用户自行对其进行排序。 报表应用程序（如 Power BI 或 Reporting Services）是此类应用程序的示例，这些应用程序允许最终用户对其数据进行排序。
尽管谨慎，但请考虑在 T-SQL 查询中存在联接时使用 LOOP JOIN 提示。 此方法可以避免使用内存授予的哈希或合并联接。 但是，仅建议使用此选项作为最后手段，因为强制联接可能会导致查询速度明显变慢。 对工作负荷进行压力测试，以确保这是一个选项。 在某些情况下，嵌套循环联接甚至可能不是一个选项。 在这种情况下，SQL Server可能会失败并出现错误MSSQLSERVER_8622“由于此查询中定义的提示，查询处理器无法生成查询计划。
内存授予查询提示
自 SQL Server 2012 SP3 以来，已有一个查询提示，可用于控制每个查询的内存授予大小。 下面是如何使用此提示的示例：
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
建议在此处使用保守值，尤其是在希望同时执行多个查询实例的情况下。 确保对工作负荷进行压力测试，以匹配生产环境，并确定要使用的值。
有关详细信息，请参阅 MAX_GRANT_PERCENT和MIN_GRANT_PERCENT。
Resource Governor
QE 内存是Resource Governor实际限制何时使用MIN_MEMORY_PERCENT和MAX_MEMORY_PERCENT设置的内存。 确定导致大量内存授予的查询后，可以限制会话或应用程序使用的内存。 值得一提的是，default工作负荷组允许查询占用最多可在SQL Server实例上授予的 25% 内存。 有关详细信息，请参阅 Resource Governor 资源池和 CREATE WORKLOAD GROUP。
自适应查询处理和内存授予反馈
SQL Server 2017 引入了内存授予反馈功能。 它允许查询执行引擎根据以前的历史记录调整授予查询的授权。 目标是尽可能减小授予的大小，或者在需要更多内存时增加授权大小。 此功能已分三波发布：
2017 SQL Server 中的批处理模式内存授予反馈
2019 SQL Server中的行模式内存授予反馈
2022 SQL Server使用查询存储和百分位授予的内存授予反馈磁盘持久性
有关详细信息，请参阅 内存授予反馈。 内存授予功能可以减少执行时查询的内存授予大小，从而减少大型授予请求导致的问题。 启用此功能后，尤其是在 SQL Server 2019 及更高版本（行模式自适应处理可用）上，你甚至可能不会注意到查询执行导致的任何内存问题。 但是，如果默认) 启用此功能 (，但仍看到大量 QE 内存消耗，请应用前面讨论的步骤来重写查询。
增加SQL Server或 OS 内存
采取相关步骤来减少查询的不必要的内存授予后，如果仍然遇到相关的内存不足问题，工作负载可能需要更多内存。 因此，如果系统上有足够的物理内存，请考虑使用 max server memory 设置增加SQL Server的内存。 请遵循有关为 OS 和其他需求保留大约 25% 内存的建议。 有关详细信息，请参阅 服务器内存配置选项。 如果系统上没有足够内存可用，请考虑添加物理 RAM，或者如果它是虚拟机，请增加 VM 的专用 RAM。
内存授予内部
若要了解有关查询执行内存的一些内部的详细信息，请参阅 了解 SQL Server 内存授予 博客文章。
最后，以下示例演示了如何模拟查询执行内存的大量消耗，以及如何引入等待 的 RESOURCE_SEMAPHORE查询。 可以执行此操作来了解如何使用本文中所述的诊断工具和技术。
请勿在生产系统上使用它。 提供此模拟可帮助你了解概念并帮助你更好地了解它。
在测试服务器上，安装 RML 实用工具和SQL Server。
使用SQL Server Management Studio等客户端应用程序将SQL Server的最大服务器内存设置降低到 1，500 MB：
EXEC sp_configure 'max server memory', 1500
RECONFIGURE
打开命令提示符，将目录更改为 RML 实用工具文件夹：
cd C:\Program Files\Microsoft Corporation\RMLUtils
使用 ostress.exe 针对测试SQL Server同时生成多个请求。 此示例使用 30 个同时会话，但可以更改该值：
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30
使用前面所述的诊断工具来识别内存授予问题。
处理大型内存授予的方法摘要
重写查询。
更新统计信息并定期更新统计信息。
为标识的查询创建适当的索引。 索引可以减少处理的大量行，从而更改 JOIN 算法并减小授权的大小或完全消除它们。
使用 OPTION (min_grant_percent = XX， max_grant_percent = XX) 提示。
使用 Resource Governor。
SQL Server 2017 和 2019 使用自适应查询处理，允许内存授予反馈机制在运行时动态调整内存授予大小。 此功能首先可以防止内存授予问题。
增加SQL Server或 OS 内存。
