排查SQL Server中的内存不足问题

症状

SQL Server使用与复杂且丰富的功能集相对应的复杂内存体系结构。 由于内存需求多种多样，内存消耗和内存压力的来源可能很多，最终导致内存不足的情况。

存在指示SQL Server内存不足的常见错误。 错误示例包括：

• 701 - 无法分配足够的内存来运行查询
• 802 - 无法获取内存以在缓冲池中分配页 (数据页或索引页)
• 1204 - 无法为锁分配内存
• 6322 - 为 XML 分析程序分配内存失败
• 6513 - 由于内存压力，无法初始化 CLR
• 6533 - AppDomain 因内存不足而卸载
• 8318 - 由于内存不足，无法加载 SQL 性能计数器
• 8356 或 8359 - ETW 或 SQL 跟踪由于内存不足而无法运行
• 8556 - 由于内存不足，无法加载 MSDTC
• 8645 - 由于内存授予 (排序和哈希) (内存，无法执行查询。有关详细信息，请参阅如何排查SQL Server错误 8645 )
• 8902 - DBCC 执行期间未能分配内存
• 9695 或 9696 - 无法为 Service Broker 操作分配内存
• 17131 或 17132 - 由于内存不足，服务器启动失败
• 17890 - 由于操作系统分页的 SQL 内存，无法分配内存
• 22986 或 22987 - 由于内存不足而发生更改数据捕获失败
• 25601 - Xevent 引擎内存不足
• 26053 - 由于内存不足，SQL 网络接口无法初始化
• 30085、30086、30094 - SQL 全文操作由于内存不足而失败

原因

许多因素都可能导致内存不足。 这些因素包括操作系统设置、物理内存可用性、在 SQL Server 中使用内存的组件，以及当前工作负荷的内存限制。 在大多数情况下，失败并出现内存不足错误的查询不是导致此错误的原因。 总体而言，原因可分为三类：

原因 1：外部或 OS 内存压力

外部压力是指来自进程外部的组件导致内存不足，导致SQL Server内存不足的内存使用率过高。 必须了解系统上的其他应用程序是否消耗了内存并导致内存可用性低。 SQL Server是为数不多的应用程序之一，旨在通过减少其内存使用量来响应 OS 内存压力。 这意味着，如果应用程序或驱动程序请求内存，OS 会向所有应用程序发送释放内存的信号，SQL Server将通过减少自身的内存使用量来做出响应。 其他应用程序很少会做出响应，因为它们不设计用于侦听该通知。 因此，如果SQL Server开始减少其内存使用量，则其内存池会减少，并且需要内存的组件都可能无法获取内存。 因此，你开始收到 701 或其他与内存相关的错误。 有关 SQL 如何动态分配和释放内存的详细信息，请参阅 SQL Server 内存体系结构。 有关此问题的更详细的诊断和解决方案，请参阅本文中的 外部内存压力 。

有三大类问题可能会导致 OS 内存压力：

• 应用程序相关问题：一个或多个应用程序一起耗尽可用的物理内存。 OS 将通过尝试释放一些内存来响应对资源的新应用程序请求。 常见方法是查找哪些应用程序耗尽内存，并采取必要的步骤来平衡其中内存，而不会导致 RAM 耗尽。
• 设备驱动程序问题：如果驱动程序错误地调用内存分配函数，设备驱动程序可能会导致所有进程的工作集分页。
• 操作系统产品问题。

有关这些步骤和故障排除步骤的详细说明，请参阅 MSSQLSERVER_17890。

原因 2：内部内存压力，不是来自SQL Server

内部内存压力是指由SQL Server进程内部因素导致的内存可用性低。 可在SQL Server进程中运行的某些组件是SQL Server引擎的“外部”。 示例包括 OLE DB 提供程序 (DLL) 链接服务器、SQLCLR 过程或函数、扩展过程 (XP) 以及 OLE 自动化 (sp_OA*) 。 其他程序包括防病毒或其他安全程序，这些程序在进程内注入 DLL 以用于监视目的。 这些组件中的任何一个问题或设计不佳都可能导致大量内存消耗。 例如，假设链接服务器将来自外部源的 2000 万行数据缓存到SQL Server内存中。 就SQL Server而言，没有内存职员会报告内存使用率过高，但SQL Server进程中消耗的内存会很高。 例如，链接服务器 DLL 的这种内存增长将导致SQL Server开始削减其内存使用率， (如上) 所示，并且会为SQL Server内的组件创建内存不足的情况，从而导致内存不足错误。 有关此问题的更详细诊断和解决方案，请参阅内部内存压力，而不是来自SQL Server。

备注

SQL Server进程空间中使用的一些 Microsoft DLL (例如 MSOLEDBSQL、SQL Native Client) 能够与SQL Server内存基础结构进行交互，以便进行报告和分配。 可以运行 select * from sys.dm_os_memory_clerks where type='MEMORYCLERK_HOST' 来获取它们的列表，并跟踪部分分配的内存消耗。

原因 3：内部内存压力，来自SQL Server组件 ()

来自SQL Server引擎内部组件的内部内存压力也可能导致内存不足错误。 有数百个组件，通过内存管理器跟踪，这些组件在SQL Server中分配内存。 必须确定哪些内存办事员 () 负责最大的内存分配才能解决此问题。 例如，如果发现 OBJECTSTORE_LOCK_MANAGER 内存管理器显示较大的内存分配，则需要了解锁管理器消耗这么多内存的原因。 你可能会发现某些查询会获取许多锁。 可以通过使用索引来优化这些查询，缩短长时间保留锁的任何事务，或者检查是否禁用了锁升级。 每个内存管理器或组件都有一种访问和使用内存的独特方式。 有关详细信息，请参阅 内存文员类型 及其说明。 有关此问题的更详细诊断和解决方案，请参阅按引擎SQL Server的内部内存使用情况。

内存压力类型的直观表示形式

下图说明了可能导致SQL Server内存不足情况的压力类型：

用于收集故障排除数据的诊断工具

可以使用以下诊断工具来收集故障排除数据：

性能监视器

使用 性能监视器 配置和收集以下计数器：

• 内存：可用 MBytes
• Process：Working Set
• 进程：专用字节数
• SQL Server：内存管理器： (所有计数器)
• SQL Server：缓冲区管理器： (所有计数器)

DMV 或 DBCC MEMORYSTATUS

可以使用 sys.dm_os_memory_clerks 或 DBCC MEMORYSTATUS 来观察SQL Server内的总体内存使用情况。

SSMS 中的内存消耗标准报表

查看SQL Server Management Studio中的内存使用情况：

1. 启动SQL Server Management Studio并连接到服务器。
2. 在“对象资源管理器”中，右键单击SQL Server实例名称。
3. 在上下文菜单中，选择“报告>标准报表>内存消耗”。

PSSDiag 或 SQL LogScout

捕获这些数据点的另一种自动化方法是使用 PSSDiag 或 SQL LogScout 等工具。

• 如果使用 PSSDiag，请将其配置为捕获 Perfmon 收集器和 自定义诊断\SQL 内存错误 收集器。
• 如果使用 SQL LogScout，请将其配置为捕获 内存 方案。

以下部分介绍每个方案的更详细步骤， (外部或内部内存压力) 。

故障排除方法

如果偶尔出现内存不足错误，或者在短时间内出现，则可能有一个生存期较短的内存问题可以自行解决。 在这些情况下，你可能不需要采取措施。 但是，如果错误在多个连接上多次发生，并且持续数秒或更长时间，请按照以下部分中的诊断和解决方案进一步排查内存错误。

外部内存压力

若要在 SQL Server 进程之外诊断系统上的内存不足情况，请使用以下方法：

• 收集性能监视器计数器。 通过查看以下计数器，调查除SQL Server以外的应用程序或服务是否正在消耗此服务器上的内存：

  • 内存：可用 MBytes
  • Process：Working Set
  • 进程：专用字节数

  下面是使用 PowerShell 收集 Perfmon 日志的示例：

  PowerShell复制

  clear $serverName = $env:COMPUTERNAME $Counters = @( ("\\$serverName" +"\Memory\Available MBytes"), ("\\$serverName" +"\Process(*)\Working Set"), ("\\$serverName" +"\Process(*)\Private Bytes") ) Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 1 | ForEach-Object { $_.CounterSamples | ForEach-Object { [pscustomobject]@{ TimeStamp = $_.TimeStamp Path = $_.Path Value = ([Math]::Round($_.CookedValue, 3)) } } }

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

  clear $serverName = $env:COMPUTERNAME $Counters = @( ("\\$serverName" +"\Memory\Available MBytes"), ("\\$serverName" +"\Process(*)\Working Set"), ("\\$serverName" +"\Process(*)\Private Bytes") )   Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 1 | ForEach-Object  { $_.CounterSamples | ForEach-Object   { [pscustomobject]@{     TimeStamp = $_.TimeStamp     Path = $_.Path     Value = ([Math]::Round($_.CookedValue, 3)) } } }

• 查看系统事件日志并查找与内存相关的错误， (例如，虚拟内存) 不足。

• 查看应用程序事件日志中与应用程序相关的内存问题。

  下面是用于查询关键字“memory”的系统和应用程序事件日志的 PowerShell 脚本示例。 随意使用其他字符串（如“资源”）进行搜索：

  PowerShell复制

  Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*" Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"

  1 2	Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*" Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"

• 解决不太重要的应用程序或服务的任何代码或配置问题，以减少其内存使用量。

• 如果除SQL Server之外的应用程序正在消耗资源，请尝试停止或重新安排这些应用程序，或考虑在单独的服务器上运行它们。 这些步骤将消除外部内存压力。

内部内存压力，不是来自SQL Server

若要诊断由模块 (DLL) SQL Server内部引起的内部内存压力，请使用以下方法：

• 如果SQL Server不使用“内存中锁定页” (AWE API) ，则其大部分内存将反映在 性能监视器 中的 Process：Private Bytes 计数器 (SQLServr实例) 中。 SQL Server：内存管理器：总服务器内存 (KB) 计数器中反映了SQL Server引擎内的总体内存使用率。 如果发现值 Process：Private Bytes 和 SQL Server：Memory Manager： Total Server Memory (KB) 之间存在显著差异，该差异可能来自 dll (链接服务器、XP、SQLCLR 等) 。 例如，如果专用字节为 300 GB，服务器总内存为 250 GB，则进程中大约 50 GB 的总内存来自SQL Server引擎外部。

• 如果SQL Server在内存中使用锁定页 (AWE API) ，则识别问题更具挑战性，因为性能监视器不提供用于跟踪单个进程的内存使用情况的 AWE 计数器。 SQL Server引擎内的总体内存使用率反映在 SQL Server：内存管理器：总服务器内存 (KB) 计数器中。 典型的 Process：Private Bytes 值可能总共在 300 MB 到 1-2 GB 之间变化。 如果发现 “进程：专用字节 ”的显著用法超出了此典型用法，则差异可能来自 DLL (链接服务器、XP、SQLCLR 等) 。 例如，如果专用字节计数器为 4-5 GB，并且SQL Server使用内存中的锁定页 (AWE) ，则大部分专用字节可能来自SQL Server引擎外部。 这是一种近似技术。

• 使用 Tasklist 实用工具标识SQL Server空间内加载的任何 DLL：

  控制台复制

  tasklist /M /FI "IMAGENAME eq sqlservr.exe"

  1	tasklist /M /FI "IMAGENAME eq sqlservr.exe"

• 还可以使用以下查询来检查加载的模块 (DLL) ，并查看是否有预期会出现某些内容。

  SQL复制

  SELECT * FROM sys.dm_os_loaded_modules

  1	SELECT * FROM sys.dm_os_loaded_modules

• 如果怀疑链接服务器模块导致大量内存消耗，可以通过禁用 “允许进程内 ”选项将其配置为进程外。 有关详细信息 ，请参阅创建链接服务器 。 并非所有链接服务器 OLE DB 提供程序都可能耗尽进程。 有关详细信息，请联系产品制造商。

• 在使用 OLE 自动化对象 () sp_OA* 的极少数情况下，可以通过仅) 指定 4 (Local (.exe) OLE 服务器，将对象配置为在 SQL Server 外部的进程中运行。 有关详细信息，请参阅 sp_OACreate。

按引擎SQL Server的内部内存使用情况

若要诊断来自SQL Server引擎内部组件的内部内存压力，请使用以下方法：

• 开始收集SQL Server的性能监视器计数器：SQL Server：缓冲区管理器**，SQL Server：内存管理器**。

• 多次查询SQL Server内存管理器 DMV，查看引擎内内存消耗量最高的位置：

  SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC

  1 2 3

  SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC

• 或者，可以在看到这些错误消息时观察更详细的 DBCC MEMORYSTATUS 输出及其更改方式。

  DBCC MEMORYSTATUS

  1	DBCC MEMORYSTATUS

• 如果在内存职员中识别出明确的违规者，请专注于解决该组件内存消耗的具体细节。 下面是几个示例：

  • 如果内存管理员 MEMORYCLERK_SQLQERESERVATIONS 消耗内存，请识别使用大量内存授予的查询，并通过索引对其进行优化，重写它们 (删除 ORDER by（例如，) ），或者应用内存授予查询提示 (查看 min_grant_percent和max_grant_percent提示 ) 。 还可以 创建资源调控器池 来控制内存授予内存的使用情况。
  • 如果缓存了大量即席查询计划，则 CACHESTORE_SQLCP 内存管理员将使用大量内存。 确定查询计划不能重用的非参数化查询，并通过转换为存储过程、使用 sp_executesql或使用 FORCED 参数化来参数化它们。 如果已启用 跟踪标志 174，则可以禁用它，看看这是否解决了问题。
  • 如果对象计划缓存存储 CACHESTORE_OBJCP 消耗过多内存，请确定哪些存储过程、函数或触发器使用大量内存，并可能重新设计应用程序。 通常，这可能是由于大量的数据库或架构，每个数据库或架构中都有数百个过程。
  • OBJECTSTORE_LOCK_MANAGER如果内存管理器显示较大的内存分配，请确定应用多个锁的查询，并使用索引对其进行优化。 缩短导致锁定在特定隔离级别长时间不释放的事务，或检查是否禁用锁升级。
  • 如果观察到非常大的 TokenAndPermUserStore (select type, name, pages_kb from sys.dm_os_memory_clerks where name = 'TokenAndPermUserStore') ，则可以使用 跟踪标志 4618 来限制缓存的大小。
  • 如果发现来自MEMORYCLERK_XTP内存文员In-Memory OLTP 的内存问题，可以参阅监视和排查In-Memory OLTP 的内存使用情况和内存优化 tempdb 元数据 (hkTempDB) 内存不足错误。

可能使内存可用快速缓解

以下操作可能会释放一些内存并将其提供给SQL Server：

更改内存配置设置

检查以下SQL Server内存配置参数，并考虑增加最大服务器内存（如果可能）：

• 最大服务器内存

• 最小服务器内存

  备注

如果发现异常设置，请根据需要更正这些设置，并考虑增加的内存要求。 “ 服务器内存配置选项”中列出了默认设置。

如果尚未配置 最大服务器内存（尤其是内存中已锁定页），请考虑将其设置为特定值，以便为 OS 提供一些内存。 请参阅 “内存中的锁定页” 服务器配置选项。

更改或移出系统的工作负荷

调查查询工作负载：并发会话数、当前正在执行的查询，并查看是否有不太重要的应用程序可以暂时停止或移动到其他SQL Server。

对于只读工作负荷，请考虑将它们移动到Always On环境中的只读次要副本。 有关详细信息，请参阅将只读工作负荷卸载到Always On可用性组的辅助副本和配置对Always On可用性组的辅助副本的只读访问权限。

确保虚拟机的内存配置正确

如果在虚拟机 (VM) 上运行SQL Server，请确保 VM 的内存不会过度提交。 有关如何为 VM 配置内存的想法，请参阅 虚拟化 - 过度使用内存以及如何在 VM 中检测内存 和 排查 ESX/ESXi 虚拟机性能问题 (内存过度使用) 。

释放SQL Server中的内存

可以运行以下一个或多个 DBCC 命令来释放多个SQL Server内存缓存：

• DBCC FREESYSTEMCACHE
• DBCC FREESESSIONCACHE
• DBCC FREEPROCCACHE

重启SQL Server服务

在某些情况下，如果需要处理严重内存耗尽，并且SQL Server无法处理查询，可以考虑重启服务。

考虑针对特定方案使用Resource Governor

如果使用 Resource Governor，建议检查资源池和工作负荷组设置，了解它们是否没有严重限制内存。

在物理或虚拟服务器上添加更多 RAM

如果问题仍然存在，则需要进一步调查，并可能增加服务器资源 (RAM) 。

排查SQL Server中内存授予导致的性能缓慢或内存不足问题

什么是内存授予？

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

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

为了提供某些上下文，查询在其生存期内可能会从不同的内存分配器或职员请求内存，具体取决于它需要执行的操作。 例如，最初分析和编译查询时，它会消耗编译内存。 编译查询后，释放该内存，生成的查询计划存储在计划缓存内存中。 缓存计划后，查询即可执行。 如果查询执行任何排序操作、哈希匹配操作 (JOIN、聚合) 或插入 COLUMNSTORE 索引，它将使用查询执行分配器的内存。 最初，查询会请求该执行内存，如果授予此内存，查询将使用全部或部分内存对结果或哈希桶进行排序。 在查询执行期间分配的内存称为内存授予。 可以想象，查询执行操作完成后，内存授予将释放回SQL Server以用于其他工作。 因此，内存授予分配本质上是暂时的，但仍可以持续很长时间。 例如，如果查询执行对内存中非常大的行集执行排序操作，则排序可能需要几秒钟或几分钟，并且授予的内存用于查询的生存期。

具有内存授予的查询示例

下面是使用执行内存的查询示例，以及显示授予的查询计划：

SQL

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

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

XML

此处需要解释几个术语。 查询可能需要一定数量的执行内存 (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)另一个计数器占可用于任何请求的最大工作区内存量，这些请求可能需要执行此类哈希、排序、大容量复制和索引创建操作。 这两个计数器之外很少遇到术语“工作区内存”。

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

在大多数情况下，当线程请求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 常规查询请求和小型查询请求) 。 有用的查询可能是：

SQL

以下示例输出显示，22 个请求使用了大约 900 MB 的查询执行内存，还有 3 个请求正在等待。 这发生在默认池 (pool_id = 2) ，常规查询信号量 (resource_semaphore_id = 0) 。

输出

性能监视器计数器

类似信息可通过性能监视器计数器获取，可在其中观察当前授予的请求 () 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 自请求放入等待队列以来的运行时间（以毫秒为单位）。 有关这些计数器的详细信息，请参阅 查询内存对象。

输出

DBCC MEMORYSTATUS 还显示有关跟踪查询执行内存的内存管理器的信息。 以下输出显示，为查询执行分配的页面 (QE) 预留超过 800 MB。

输出

内存管理员 DMV sys.dm_os_memory_clerks

如果需要更多表格结果集（不同于基于 DBCC MEMORYSTATUS节的 ），则可以将 sys.dm_os_memory_clerks 用于类似信息。 MEMORYCLERK_SQLQERESERVATIONS查找内存文员。 但是，查询内存对象在此 DMV 中不可用。

SQL

下面是示例输出：

输出

使用扩展事件 (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 获取数据，并提取查询文本和查询计划作为结果：

SQL

下面是活动 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。

输出

使用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 查询。 下面是一个查询，其中显示了授予的请求和等待的请求：

SQL

示例输出显示已向两个请求授予内存，另有 20 个请求正在等待授予。 列 granted_query_memory 报告大小（以 8 KB 为单位）。 例如，值 34，709 表示授予的内存为 34，709 * 8 KB = 277，672 KB。

输出

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。