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

项目
03/20/2024

症状

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：由于 OS 分页出 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中的内存使用情况：

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

PSSDiag 或 SQL LogScout

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

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

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

故障排除方法

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

外部内存压力

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

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

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

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

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)) }
}
}

查看系统事件日志并查找与内存相关的错误， (例如，虚拟内存) 不足。
查看应用程序事件日志中与应用程序相关的内存问题。

下面是一个 PowerShell 脚本示例，用于查询系统和应用程序事件日志以获取关键字 (keyword) “内存”。随意使用其他字符串（如“资源”）进行搜索：
```
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"
```
还可以使用以下查询来检查加载的模块 (DLL) ，并查看是否存在任何意外情况。
```
SELECT * FROM sys.dm_os_loaded_modules
```
如果怀疑链接服务器模块导致大量内存消耗，可以通过禁用 “允许进程内 ”选项将其配置为进程外。有关详细信息，请参阅创建链接服务器。并非所有链接服务器 OLE DB 提供程序都可能耗尽进程。有关详细信息，请联系产品制造商。
在使用 OLE 自动化对象 () sp_OA* 的极少数情况下，可以通过将本地 (.exe) OLE 服务器仅) 指定 4 (上下文值，将对象配置为在 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

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

可能使内存可用快速缓解

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

更改内存配置设置

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

最大服务器内存
最小服务器内存

注意

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

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

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

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

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

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

如果在虚拟机 (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) 。

通过