解决 SQL Server 中内存授予导致的性能低下或内存不足问题

Summary

内存授予是SQL Server在查询执行时为排序、哈希、大容量复制和索引创建操作分配的内存。 当许多查询同时请求较大的内存授予时,其他查询会等待内存(RESOURCE_SEMAPHORE 等待),这会导致性能变慢和低内存状态。

本文介绍什么是内存授予量,以及如何在服务器级别识别内存授予量等待。 它还演示如何查找使用或等待内存授予的特定查询,以及如何通过优化查询、使用查询提示、配置Resource Governor和使用内存授予反馈来减少大型授予。

什么是内存授予量?

内存授予(也称为查询执行(QE)预留、查询执行内存、工作区内存和内存预留)描述查询执行时内存的使用情况。 SQL Server 在查询执行期间为以下一个或多个目的分配此内存:

  • 排序操作
  • 哈希操作
  • 大容量复制操作(不是常见问题)
  • 索引创建,包括插入列存储索引,因为哈希字典/表在运行时用于索引生成(不是常见问题)

查询在其生存期内如何使用内存

在其生存期内,查询可能会根据需要执行的操作从不同的内存分配器或内存职员请求内存。 查询在初始解析和编译时,将消耗编译内存。 编译查询后,释放该内存,生成的查询计划将存储在计划缓存内存中。 计划被缓存后,查询即可执行。

如果查询执行任何排序操作、哈希匹配操作(JOIN 或聚合),或插入列存储索引,则它使用查询执行分配器的内存。 一开始,查询会请求该执行内存。 如果授予内存,查询会将其中的所有或部分用于排序结果或哈希桶。 在查询执行期间分配的此内存称为内存授予量。

查询执行操作完成后,内存授予将释放回SQL Server用于其他工作。 因此,内存授予分配在本质上是暂时性的,但仍可以持续很长时间。 例如,如果查询对内存中非常大的行集执行排序操作,则排序可能需要几秒钟或几分钟时间,并且授予的内存用于查询的生存期。

具有内存授予的查询示例

下面是使用执行内存及其查询计划的查询示例,其中显示了内存授予:

SELECT * 
FROM sys.messages
ORDER BY message_id

此查询选择超过 300,000 行的行集并进行排序。 排序操作会引发内存授予量请求。 如果在 SQL Server Management Studio (SSMS) 中运行此查询,可以查看其查询计划。 选择查询计划最 SELECT 左侧的运算符时,可以查看查询的内存授予量信息(按 F4 显示 属性):

带有内存授予和查询计划的查询的屏幕截图。

此外,如果在查询计划的空白区域中右键单击,可以选择“显示执行计划 XML...”,并找到显示相同内存分配信息的 XML 元素。

 <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 向正在执行的查询授予其所请求的内存时,这称为一次内存授予。 有几个性能计数器使用术语“grant”。这些计数器 Memory Grants Outstanding 和 Memory Grants Pending 显示已满足或正在等待的内存授权数量。 它们不考虑内存授予量的大小。 单独使用一个查询可以消耗 4 GB 内存来执行排序,但这不会反映在这两个计数器中的任何一个中。

  • 工作区内存 是描述相同内存的另一个术语。 通常,你可能会在 Perfmon 的计数器 Granted Workspace Memory (KB) 中看到这个术语,它反映了当前用于排序、哈希、大容量复制和索引创建操作的内存总量(以 KB 表示)。 Maximum Workspace Memory (KB)另一个计数器表示任何可能需要执行此类哈希、排序、大容量复制和索引创建操作的请求可用的最大工作区内存量。 除了这两个计数器外,很少会遇到“工作区内存”这一术语。

大型 QE 内存利用率的性能影响

在大多数情况下,当线程请求 SQL Server 中的内存完成操作且内存不可用时,请求失败并出现内存不足错误。 但是,存在一些异常情况,其中线程不会失败,而是等待内存可用。 其中一种场景是内存分配,另一种场景是查询编译内存。

SQL Server 使用名为 信号量 的线程同步对象来跟踪为查询执行授予了多少内存。 如果 SQL Server 耗尽了预定义的 QE 工作区,查询不会因内存不足错误而失败,而是会等待。 鉴于允许工作区内存占用大量总体 SQL Server 内存,因此在此空间中等待内存会产生严重的性能影响。

当大量并发查询请求执行内存并同时耗尽 QE 内存池时,或者当一些并发查询各自请求非常大的内存授予时,将发生等待。 无论哪种方式,由此产生的性能问题都可能导致以下症状:

  • 缓冲区缓存中的数据页和索引页可能已被刷新出去,以便为大型内存授予腾出空间。 这意味着来自查询请求的页面读取必须从磁盘读取(操作速度明显较慢)。
  • 其他内存分配的请求可能会失败并出现内存不足错误,因为资源被排序、哈希或索引生成操作占用。
  • 需要执行所需内存的请求正在等待资源可用,并且需要很长时间才能完成。 换句话说,对最终用户来说,这些查询速度较慢。

因此,如果在 Perfmon、动态管理视图(DMV)或者 DBCC MEMORYSTATUS 中观察到查询执行内存等待,则必须采取措施解决此问题,特别是如果问题频繁发生。 若要了解如何操作,请参阅 如何减少排序和哈希操作的内存授予量。

如何识别内存授予等待

从服务器级别开始,确认查询正在等待查询执行 (QE) 内存。 然后向下钻取到需要优化或重写的单个查询。 选择最适合你的方法。

以下方法显示服务器级别的聚合内存授予使用情况:

以下方法标识使用或等待内存授予的各个查询:

如何在服务器级别监视内存授予使用情况

使用以下方法查看聚合内存授予使用情况以及整个 SQL Server 实例的等待情况。 某些方法可能在你的环境中不可用。 例如,Perfmon 在 Azure SQL 数据库中不可用。

资源信号量 DMV sys.dm_exec_query_resource_semaphores

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)

性能监视计数器

可以通过SQL Server内存管理器对象性能监视器(Perfmon)计数器找到类似的信息。 可以观察当前授予的请求(Memory Grants Outstanding)、等待授予的请求(Memory Grants Pending)和内存授予所使用的内存量(Granted Workspace Memory (KB))。

在以下屏幕截图中,未完成的内存授予为 18,挂起的内存授予为 2,并且已授予的工作区内存为 828,288 KB。 Memory Grants Pending 计数器的非零值表示内存已用尽。

性能监视器 的屏幕截图,显示内存授予未完成、挂起以及已授予的工作区内存。

使用以下计数器:

  • SQLServer,内存管理器:最大工作区内存(KB)
  • SQLServer, 内存管理器: 未完成的内存授予请求
  • SQLServer,内存管理器:内存授予挂起
  • SQLServer,内存管理器:授予的工作区内存(KB)

DBCC MEMORYSTATUS

可在其中查看查询预留内存的详细信息的另一个位置是 DBCC MEMORYSTATUS (查询内存对象部分)。 可以查看用户查询的 Query Memory Objects (default) 输出。 例如,如果启用了名为 PoolAdmin 的资源池的资源调控器,则可以同时查看 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)

多个 扩展事件(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。)使用此事件可识别由于排序操作而缓慢执行的查询,尤其是当 warning_type = 2 时,表明需要多次遍历数据才能完成排序。

包含内存授予信息的计划生成事件

以下生成查询计划的扩展事件默认包含 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

列存储索引生成

扩展事件还涵盖列存储索引生成期间使用的执行内存。 以下事件可用:

  • 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 行组生成期间的内存估计结果。

如何识别使用或等待内存授予的查询

在单个请求级别,可以找到两种类型的查询:使用大量查询执行内存的查询,以及等待该内存的查询。 使用以下 DMV 和 查询存储 识别查询及所请求的执行内存大小。

等待查询可能具有适度的内存需求,在这种情况下,你可以将注意力集中在其他位置。 如果他们请求巨大的内存大小,他们也可能是罪魁祸首。

通常会发现,某个特定查询是问题所在,但它的许多实例都在运行。 获取内存授予的实例会导致同一查询的其他实例等待授予。

使用 sys.dm_exec_query_memory_grants 标识特定查询

若要查看单个请求及其请求和授予的内存大小,请查询 sys.dm_exec_query_memory_grants 动态管理视图(DMV)。 此 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 是下一个获得资助的,如 wait_order = 1 所示,其后是会话 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 识别特定查询语句

等待类型RESOURCE_SEMAPHORE指示查询正在等待内存分配授予。 可以在 sys.dm_exec_requests中观察单个请求的此等待类型。 此 DMV 是确定哪些查询是内存授予不足的受害者的最佳起点。

还可以在 sys.dm_os_wait_stats 中观察到 RESOURCE_SEMAPHORE wait(作为 SQL Server 级别的聚合数据点)。 当无法授予查询内存请求时,将显示此等待类型,因为其他并发查询已占用内存。 等待请求数高,等待时间长,表示使用执行内存或大型内存请求大小的并发查询过多。

注:

内存授予的等待时间有限。 在过度等待(例如超过 20 分钟)后,SQL Server 会使查询超时并引发错误 8645,“等待内存资源执行查询时发生超时。 重新运行查询。可以通过在中查看来查看服务器级别设置的超时值。 超时值在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'

示例输出显示已向两个请求授予内存,另外二十四个请求正在等待授予。 该 granted_query_memory 列以 8 KB 页为单位报告大小。 例如,值为 34,709 表示已授予 277,672 KB 的内存,即 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 可以提供其他 DMV 无法提供的见解。

以下示例查询查找使用最大执行内存量的前 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 min_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)具有大小限制和保留策略。 有关详细信息,请参阅 管理 查询存储 的最佳做法 中的“设置最佳 查询存储 捕获模式”和“在 查询存储 中保留最相关的数据”两节。

  1. 使用此查询确定数据库是否已启用查询存储:

    SELECT name, is_query_store_on 
    FROM sys.databases
    WHERE is_query_store_on = 1
    
  2. 在要调查的特定数据库的上下文中运行以下诊断查询:

    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 0 NULL 9 9 7 1 1
60 0 NULL 9 9 7 1 2
75 1310085 RESOURCE_SEMAPHORE 40 NULL 0 NULL 1
75 1310085 RESOURCE_SEMAPHORE 40 NULL 0 NULL 2
86 1310129 RESOURCE_SEMAPHORE 40 NULL 0 NULL 1
86 1310129 RESOURCE_SEMAPHORE 40 NULL 0 NULL 2

示例输出清楚地显示,由 session_id = 60 提交的查询已成功获得请求的 9 MB 内存授予,但只需 7 MB 即可启动查询执行。 最后,查询只使用了从服务器收到的 9 MB 中的 1 MB。 输出还显示会话 75 和 86 正在等待内存授予,这就是 wait_type 为 RESOURCE_SEMAPHORE 的原因。 他们的等待时间已经超过 1,300 秒(21 分钟),其 granted_memory_mb 为 NULL。

此诊断查询是一个示例,因此可以随意根据需要以任何方式对其进行修改。

内存授予问题的诊断工具

若要诊断内存授予问题,请使用 SQL LogScout 从前面所述的 DMV 和性能监视器计数器收集输出。

使用 LightPerf、GeneralPerf 或 DetailedPerf 方案运行 SQL LogScout 以收集必要的日志。 在 YourServer_PerfStats.out 文件中,查找 -- dm_exec_query_resource_semaphores -- 和 -- dm_exec_query_memory_grants -- 输出。

或者,使用 SQL Nexus 将 SQL LogScout 输出导入SQL Server数据库。 SQL Nexus 创建两个表 tbl_dm_exec_query_resource_semaphores 和 tbl_dm_exec_query_memory_grants,其中包含诊断内存授予所需的信息。

SQL LogScout 还会将性能监视器日志收集为 .blg 文件。 使用这些文件查看性能监视器计数器部分中所述的计数器。

为什么内存授予对开发人员和 DBA 很重要

内存授予问题往往是一些最常见的与内存相关的问题。 应用程序通常执行看似简单的查询,这些查询最终会导致SQL Server因大型排序或哈希操作而导致性能问题。 此类查询不仅消耗大量SQL Server内存,还会导致其他查询等待内存可用,从而造成性能瓶颈。

通过使用本文中所述的工具(DMV、Perfmon 计数器和实际查询计划),可以识别哪些查询使用大量内存授予。 然后,可以优化或重写这些查询,以解决该内存使用问题或减少工作区内存使用量。

如何减少排序和哈希操作的内存授予

确定使用大量查询预留内存的特定查询后,可以通过重新设计这些查询来采取措施来减少内存授予量。

查询中排序和哈希操作的原因

第一步是了解查询中的哪些操作可能会导致内存授予量。

查询使用 SORT 运算符的原因

以下 T-SQL 元素可能会导致查询计划中出现 Sort 运算符:

  • ORDER BY (T-SQL) 导致行在作为最终结果流式传输之前进行排序。

  • 如果基础索引不存在对分组列进行排序,GROUP BY (T-SQL)可能会在查询计划中引入排序运算符。

  • DISTINCT (T-SQL) 的行为类似于 GROUP BY. 若要标识不同的行,先对中间结果排序,然后删除重复项。 如果数据尚未因有序索引查找或扫描而排序,则优化器会在此运算符之前使用 Sort 运算符。

  • 当被查询优化器选中时,合并联接运算符要求两个联接输入都已排序。 如果聚集索引在其中一个表中的联接列上不可用,SQL Server可能会触发排序。

查询会使用 HASH 查询计划运算符的原因

此列表并不详尽,但包括哈希操作中最常见的一些情形。 分析查询计划 以标识哈希匹配操作。 以下 T-SQL 元素可能导致出现 Hash Match 运算符:

  • JOIN (T-SQL):联接表时,SQL Server在三个物理运算符(Nested Loop和Merge JoinHash Join)之间进行选择。 如果 SQL Server 最终选择了 哈希联接,则需要 QE 内存来存储和处理中间结果。 通常,缺少良好的索引会导致这种资源开销最高的联接运算符 Hash Join。 要Hash Match以识别,请参阅逻辑和物理运算符参考。

  • DISTINCT (T-SQL): Hash Aggregate 运算符可用于消除行集中的重复项。 若要在查询计划中查找运算符(Hash MatchAggregate),请参阅逻辑运算符和物理运算符参考。

  • UNION (T-SQL):此运算符类似于 DISTINCT。 A Hash Aggregate 可用于删除此运算符中的重复项。

  • SUM/AVG/MAX/MIN (T-SQL):任何聚合操作都可以作为一种 Hash Aggregate执行。 若要在查询计划中查找运算符(Hash MatchAggregate),请参阅逻辑运算符和物理运算符参考。

了解这些常见原因有助于尽可能消除 SQL Server 中出现的大型内存授予请求。

减少排序和哈希操作或内存授予大小的方法

以下技术可以减少排序和哈希操作,或减少内存授予量的大小:

  • 使统计信息保持最新。 这一基本步骤可提高多个层面的查询性能,可确保在选择查询计划时查询优化器具有最准确的信息。 SQL Server 根据统计信息确定要申请的内存授予大小。 过时的统计信息可能会导致对内存授予请求的高估或低估,从而分别导致不必要的过高内存授予请求,或将结果写入磁盘。 确保数据库中启用了自动更新统计信息,和/或使用 UPDATE STATISTICS 或 sp_updatestats 更新统计信息。
  • 减少来自表的行数。 如果使用限制性更高的 WHERE 筛选器或 JOIN 并减少行数,查询计划中后续的排序操作可对较小的结果集进行排序或聚合。 较小的中间结果集需要更少的工作集内存。 这是一个通用规则,开发人员不仅可遵循这一通用规则来节省工作集内存,还可以减少 CPU 和 I/O(此步骤并不总是可能的)。 如果编写良好且资源高效的查询已到位,则已满足此准则。
  • 在联接列上创建索引以帮助合并联接。 查询计划中的中间操作受基础表上的索引影响。 例如,如果表对联接列没有索引,并且合并联接是最具成本效益的联接运算符,则执行联接之前,必须对该表中的所有行进行排序。 如果列上存在索引,则可以消除排序操作。
  • 创建索引以帮助避免哈希操作。 通常,基本查询优化首先检查查询是否具有适当的索引,以帮助他们减少读取,并尽可能减少或消除大型排序或哈希操作。 通常选择哈希联接来处理大型、未排序和非索引输入。 创建索引可能会更改此优化器策略并加快数据检索速度。 有关创建索引的帮助,请参阅数据库引擎优化顾问和根据缺失索引建议优化非聚集索引。
  • 对使用 GROUP BY 的聚合查询,在适当情况下使用列存储索引。 处理非常大的行集并通常执行“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 内存是资源调控器在使用MIN_MEMORY_PERCENT和MAX_MEMORY_PERCENT设置时实际限制的内存。 确定导致大量内存授予的查询后,可以限制会话或应用程序使用的内存。

default工作负荷组允许查询占用在 SQL Server 实例上可授予的内存中的最多 25%。 有关详细信息,请参阅 资源调控器资源池 和 CREATE WORKLOAD GROUP。

使用自适应查询处理和内存授予反馈

SQL Server 2017 引入了内存授予反馈功能。 它允许查询执行引擎根据以往历史记录调整提供给查询的资源授予。 目标是尽可能减小授予内存量的大小,或在需要更多内存时增加它。 此功能在三波中发布:

  1. SQL Server 2017 中的批处理模式内存授予反馈
  2. SQL Server 2019 中的行模式内存授予反馈
  3. 使用 SQL Server 2022 中的查询存储和百分位授予的内存授予反馈磁盘持久化

有关详细信息,请参阅内存授予反馈。 此功能可以减少执行时查询的内存授予大小,从而减少大型授予请求导致的问题。

使用此功能时,尤其是在 SQL Server 2019 及更高版本中行模式自适应处理可用时,可能不会注意到由查询执行引起的任何内存问题。 但是,如果该功能处于打开状态(默认值),但仍会看到较大的 QE 内存消耗,请应用前面讨论的步骤来重写查询。

增加 SQL Server 或 OS 内存

执行步骤来减少查询的不必要的内存授予后,如果仍遇到相关的内存不足问题,工作负荷可能需要更多内存。 因此,如果系统上有足够的物理内存,请考虑使用max server memory设置增加SQL Server内存。 请遵循有关为 OS 和其他需求留下约 25% 内存的建议。 有关详细信息,请参阅 服务器内存配置选项。 如果系统没有足够的可用内存,请考虑添加物理 RAM。 如果是虚拟机,请增加为 VM 分配的内存。

内存授予在内部的工作原理

有关查询执行内存内部的详细信息,请参阅 了解 SQL Server 内存授予量 博客文章。

如何创建内存授予使用量较高的性能场景

以下示例演示如何模拟大量消耗查询执行内存,并使查询在 RESOURCE_SEMAPHORE上等待。 使用此模拟了解如何使用本文中所述的诊断工具和技术。

警告

请勿在生产系统上使用此项。 提供此模拟,可帮助你了解概念并帮助你更好地了解它。

  1. 在测试服务器上,安装 RML 实用工具 和 SQL Server。

  2. 使用 SQL Server Management Studio 等客户端应用程序将 SQL Server 的最大服务器内存设置为 1,500 MB。

    EXEC sp_configure 'max server memory', 1500
    RECONFIGURE
    
  3. 打开命令提示符并将目录更改为 RML 实用工具文件夹。

    cd C:\Program Files\Microsoft Corporation\RMLUtils   
    
  4. 使用 ostress.exe 针对测试 SQL Server 发起多个并发请求。 此示例使用 30 个同时会话,但可以更改该值。

    ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30
    
  5. 使用前面所述的诊断工具确定内存授予量问题。

快速参考:减少大型内存授予量的方法

以下列表汇总了本文中介绍的用于减少大型内存授予量的选项:

  • 重写查询。
  • 更新统计信息并定期更新统计信息。
  • 为已识别的一个或多个查询创建适当的索引。 索引可能会减少处理的大量行,从而更改 JOIN 算法并减小内存授予大小或完全消除这些内存授予。
  • 使用 OPTION(min_grant_percent = XX, max_grant_percent = XX)提示。
  • 使用 Resource Governor。
  • 依赖于 SQL Server 2017 及更高版本中的内存授予反馈。 此机制根据先前的执行调整内存授予量大小,并且还可能从一开始就避免内存授予量问题。 在 SQL Server 2022 及更高版本中,它会在 查询存储 中保留反馈并使用百分位内存授予。
  • 增加 SQL Server 或 OS 内存。