如何减少 SQL Server 中缓冲池内存的分页

适用于: Microsoft SQL Server 2005 Standard X64 EditionMicrosoft SQL Server 2005 Enterprise X64 EditionSQL Server 2008 Standard

摘要


Microsoft SQL Server 根据系统上当前负载和活动的内存要求执行动态内存管理。在 Windows 上,SQL Server 可以使用由查询内存资源通知 Windows API 提供的内存通知机制。根据查询内存资源通知 Windows API 或内存计算中的此信息,SQL Server 响应特定系统上的当前内存状况。这提供了以下好处:
  • 系统不会分页 SQL Server 进程的工作集。
  • 内存中提供了必要的数据库页,以减少物理 I/O 需求。
有关详细信息,请参阅 SQL 服务器联机丛书中的"动态内存管理"主题和"服务器内存选项"主题。

症状


请考虑 SQL Server 中可能出现的以下问题:
  • SQL Server 的性能突然下降。
  • 运行 SQL Server 的计算机在短时间内停止响应。
  • 对于连接到 SQL Server 的应用程序,将出现超时。
  • 当您运行简单命令或在系统上使用应用程序时,会出现问题。
当出现以下问题时,以下错误消息之一可能会记录在 SQL Server 错误日志中:
错误消息 1
DateTime spid1s sql 服务器进程内存的很大一部分已被分页。这可能会导致性能下降。时间:0 秒。工作集 (KB): 1086400, 已提交 (KB): 2160928, 内存利用率: 50%。
错误消息 2
DateTime spid1s sql 服务器进程内存的很大一部分已被分页。这可能会导致性能下降。时间: 315 秒。工作集 (KB): 410156, 已提交 (KB): 2201296, 内存利用率: 18%。
错误消息 3
DateTime spid1s sql 服务器进程内存的很大一部分已被分页。这可能会导致性能下降。时间: 646 秒。工作集 (KB): 901904, 已提交 (KB): 2215752, 内存利用率: 40%。
Microsoft SQL Server 支持团队还观察到 SQL Server 错误日志或 Windows 事件日志中记录的其他错误消息或警告。这些消息类似于以下内容:
DateTime Server      Resource Monitor (0x13c43) Worker 0x0412C1E8 appears to be non-yielding on Node 0. Memory freed: 34152 KB. Approx CPU Used: kernel 171 ms, user 140 ms, Interval: 125093.DateTime Server      * *******************************************************************************DateTime Server      * BEGIN STACK DUMP:DateTime Server      *   DateTime spid 0DateTime Server      * Non-yielding Resource MonitorDateTime Server      * *******************************************************************************DateTime Server      * *******************************************************************************DateTime Server      * BEGIN STACK DUMP:DateTime Server      *   DateTime spid 0DateTime Server      * Non-yielding IOCP ListenerDateTime Server      * *******************************************************************************DateTime spid2s      LazyWriter: warning, no free buffers found.DateTime spid4s      AppDomain xx (SQLCLR.dbo[runtime].xx) is marked for unload due to memory pressure.DateTime spid4s      AppDomain xx (SQLCLR.dbo[runtime].xx) unloaded.DateTime Logon       Error: 17189, Severity: 16, State: 1.DateTime Logon       SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: xx.xxx.xx.xx]Event Type:ErrorEvent Source:SQLBrowserEvent ID:8Description: The SQLBrowser service was unable to process a client request.  
这些附加错误消息或警告可能与"sql 服务器进程内存的很大一部分已被分页"错误消息一起出现。在一些情况下,这些附加错误消息或警告未与错误消息一起显示。如果您注意到这些错误消息或警告之一,请考虑本文中讨论的 SQL Server 工作集分页作为一个可能的原因,而不是唯一的原因。由于各种其他条件或原因,可能会记录这些额外的错误消息或警告。

原因


出现此问题的原因是 Windows 操作系统将 SQL Server 进程的工作集分页。当 SQL Server 进程的工作集达到提交到 SQL Server 进程的内存的 50% 或更少时,将记录这些错误消息。可以使用这些错误消息来确定 SQL Server 性能显著降低的情况,因为 Windows 操作系统将 SQL Server 进程的工作集分页。此外,这些错误消息在前 30 分钟内每五分钟记录一次。在前 30 分钟之后,这些错误消息的频率加倍,直到频率达到一天的最大频率。发生此问题时,您可能会注意到系统上其他应用程序的工作集也在同一时间分页。有关详细信息,请访问以下内容:

SQL 服务器工作集消息

另外,SQL Server 中存在已知问题,您可能会在其中收到此错误消息的实例。在 SQL Server 的启动过程中,错误消息将记录在 SQL Server 错误日志中。错误消息可能是错误警告。因此,它可能并不表示工作集修剪实际上已发生。有关详细信息,请访问以下内容:

SQL Server 报告在早期启动阶段的工作集修剪警告消息

在 VM 设置中过度分配内存。例如,HyperV 或 VMWare 气球驱动程序问题。有关详细信息,请访问以下内容:

1003470气球驱动程序保留内存,导致虚拟机来宾操作系统性能问题

2001003故障排除 ESX/ESXi 虚拟机性能问题

解决方法


在尝试解决此问题之前,请执行"如何解决此问题"部分中的步骤来解决此问题。如果仍然遇到此问题,可以通过锁定为物理内存中的缓冲池分配的内存来防止 Windows 操作系统分页 SQL Server 进程的缓冲池内存。通过将内存用户中的 Lock 页直接分配给用作 SQL Server 服务的启动帐户的用户帐户来锁定内存。

备注使用内存中的锁定页可确保 SQL Server 管理的内存未分页。但是,线程堆栈、EXE 和任何 DLL 映像、堆内存、CLR 内存仍可以由操作系统分页。备注由于 SQL Server 2008 SP1 累积更新 2 SQL Server 标准版和企业版都可以在内存用户右侧使用锁定页。有关支持锁定页面的详细信息,请单击以下文章编号以查看 Microsoft 知识库中的文章:

970070支持在 SQL Server 2005 标准版和 SQL Server 2008 标准版上锁定页面
要将锁定页分配给内存用户右侧,请按照以下步骤操作:
  1. 单击"开始",单击"运行",键入gpedit.msc,然后单击"确定"。备注将显示"组策略"对话框。
  2. 展开"计算机配置",然后展开"Windows 设置"。
  3. 展开安全设置,然后展开 本地政策.
  4. 单击"用户权限分配",然后双击"锁定内存中的页面"。
  5. 在"本地安全策略设置"对话框中,单击"添加用户或组"。
  6. 在"选择用户或组"对话框中,添加具有运行 Sqlservr.exe 文件的帐户,然后单击 好的。
  7. 关闭"组策略"对话框。
  8. 重新启动 SQL Server 服务。
锁定页分配给内存用户右侧并重新启动 SQL Server 服务后,Windows 操作系统将不再在 SQL Server 进程中页出缓冲池内存。但是,Windows 操作系统仍然可以在 SQL Server 进程中打印出非缓冲池内存。通过确保以下消息在启动时写入 SQL Server 错误日志,可以验证 SQL Server 实例是否使用了用户权利:
"使用缓冲池的锁定页"
此消息仅适用于 SQL Server。有关此消息的详细信息,请访问以下内容:当 Windows 操作系统挖出非缓冲池内存时,您仍然可能会遇到性能问题。但是,在 SQL Server 错误日志中未记录"症状"部分中提到的错误消息。

更多信息


如何解决此问题

有三大类问题可能导致此问题:

  1. 与应用程序相关的问题:所有应用程序一起耗尽了可用的物理内存,操作系统必须释放一些内存,以便对资源进行新的应用程序请求。通常,此处的方法是查找哪些应用程序耗尽了内存,并采取必要步骤来平衡它们之间的内存,而不会导致 RAM 耗尽。
  2. 设备驱动程序问题:如果驱动程序调用内存分配功能不正确,设备驱动程序可能会导致所有进程的工作集分页。
  3. 操作系统问题
要解决此问题,请按照以下步骤操作:
  1. 与应用程序相关的问题:

    应用程序一起可能会消耗系统上的所有 RAM。如果发出新的内存请求,操作系统需要满足它们,如果没有空闲内存,它将修剪正在运行的应用程序的工作集以满足内存请求。在这种情况下,您可能会发现大多数(如果不是所有)应用程序的工作集会显著下降。要遵守此操作,请收集系统上所有应用的以下性能监视器计数器:

    性能对象:流程 计数器:工作集

    此外,监视以下计数器以关联系统上可用的物理内存量。

    性能对象:内存 计数器:可用内存 (MB)

    您可能会观察到的典型行为是减少接近 0 MB 的可用内存,同时系统上大多数(所有)进程的工作集计数器突然下降。如果观察到此类行为,则可能需要采取措施减少系统上的内存使用量,其中包括减少 SQL Server 的最大服务器内存。

    应用程序也可能过多地使用系统缓存,并可能导致系统缓存大量增长。为了响应系统缓存的增长,系统将 SQL Server 进程或其他应用程序的工作集分页。如果遇到此问题,可以在应用程序中使用一些内存管理功能。这些函数控制文件 I/O 操作可在应用程序中使用的系统缓存空间。例如,可以使用 SetSystemFileCacheSize 函数和 GetSystemFileCacheSize 函数来控制文件 I/O 操作可以使用的系统缓存空间。 可以使用内存性能对象查看此对象中各种计数器的值,以确定系统缓存工作集是否使用过多的内存。例如,您可以查看缓存字节和系统缓存驻留字节计数器。有关此主题的详细信息,请访问以下 Microsoft 网站:

    太多缓存微软视窗动态缓存服务976618  当系统文件缓存占用大部分物理 RAM 时,应用程序和服务中会遇到性能问题

    您可以下载和部署"微软 Windows 动态缓存服务",以控制系统缓存消耗的内存。

  2. 设备驱动程序问题:

    如果设备驱动程序使用MmAllocateContigimIMimimimim 函数,并且将最高可接受地址参数的值设置为小于 4 GB,则 Windows 操作系统可能会打印系统上的进程的工作集,包括 SQL Server 进程。要解决此问题,请与设备驱动程序供应商联系以获取驱动程序更新。

    当设备驱动程序尝试分配内存时,Windows 操作系统可能会分页出其他应用程序的工作集。此 Windows 修补程序允许您使用事件跟踪来查找导致问题的设备驱动程序。要查找有关导致工作集修剪行为的特定驱动程序的详细信息,请参阅 MSDN 文章"标识分配连续内存的驱动程序"。

  3. 操作系统问题: 要解决导致 Windows 操作系统打印出 SQL Server 进程的工作集的已知问题,请应用以下 Microsoft 知识库文章中介绍的修补程序。 

    备注修补程序是累积的。修补程序的更高版本包含该修补程序的早期版本。

  • 当系统使用某些高级 TCP 功能时,可能会修剪 SQL Server 集。有关详细信息,请单击以下文章编号以查看 Microsoft 知识库中的文章:

942861 应用程序连接到运行 Windows 服务器的服务器上的 SQL Server 时错误消息:"常规网络错误"、"通信链路故障"或"传输级错误"

  • 在运行 Windows Server 2003 的多处理器计算机上,系统进程可能会消耗大部分可用系统内存。有关详细信息,请单击以下文章编号以查看 Microsoft 知识库中的文章:

942486 系统进程可能会消耗运行 Windows Server 2003 的多处理器计算机上的大多数可用系统内存

  • 如果 SQL Server 与 SAP R/3 一起运行,则可能会遇到分页问题。有关详细信息,请单击以下文章编号以查看 Microsoft 知识库中的文章:

931308在基于 Windows Server 2003 的计算机上运行 SAP R/3 程序时,可能会遇到硬盘分页增加

  • 如果在 Windows Server 2008 上运行 SQL Server,则必须对可能导致工作集修剪或其他操作系统组件不必要地过度使用内存的已知问题应用修补程序。有关详细信息,请单击以下文章编号以查看 Microsoft 知识库中的文章:

971714当您使用活动目录诊断模板运行 Perfmon.exe 以在基于 Windows Server 2008 的域控制器上生成报告时,报表生成过程可能会停止响应

  • 如果在 Windows 服务 2008 R2 上运行 SQL Server,则必须对可能导致工作集修整的已知问题应用修补程序。有关详细信息,请单击以下文章编号以查看 Microsoft 知识库中的文章:

979149运行 Windows 7 或 Windows 服务器 2008 R2 的计算机在运行大型应用程序时无响应

2155311如果线程请求的内存量在前 4 GB 内存范围内,则具有基于 NUMA 处理器且正在运行 Windows Server 2008 R2 或 Windows 7 的计算机上出现性能不佳的情况2468345当 Windows Server 2008 R2 中使用 Storport 驱动程序时,计算机间歇性地执行不佳或停止响应

如何确定 SQL Server 使用的内存

您可以使用性能监视器查看 SQL Server 在未启用内存中锁定的页面时使用的内存。

SQL Server 还使用以下性能计数器来公开缓冲池分配的内存:

性能对象:SQL 服务器:内存管理器计数器:服务器总内存(KB)

备注如果 SQL Server 的实例是命名实例,则性能对象的名称如下所示:

MSSQL$实例名称: 内存管理器

此外,您可以通过监视以下性能计数器来测量分页对 SQL Server 的影响:

性能对象:流程计数器:私有字节实例:sqlservr性能对象:流程计数器:工作集实例:sqlservr

专用字节计数器测量当前提交的虚拟内存。工作集计数器测量当前提交的虚拟内存占用物理内存 (RAM) 的数量。

如果工作集计数器的值小于总服务器内存 (KB) 计数器(或专用字节)的值,则至少从 SQL Server 工作集中修剪了缓冲区部分的一些内存。

您还可以通过监视以下性能计数器来观察系统上的整体分页(工作集修整),该计数器显示从磁盘读取和写入的页面:

性能对象:内存计数器:页面输入/秒和页面输出/秒

有关如何使用此计数器确定内存页的详细信息,请单击以下文章编号以查看 Microsoft 知识库中的文章:

889654 如何为 64 位版本的 Windows Server 2003 或 Windows XP 确定适当的分页文件大小 
在内存用户中正确分配Lock 页并重新启动 SQL Server 服务后,SQL Server 进程的缓冲池仍响应内存资源通知事件,并动态增加或减少这些事件。但是,在以下性能计数器中看不到锁定在内存中的缓冲池的内存分配:
  • 性能监视器中的专用字节计数器和工作集计数器
  • 任务管理器中"进程"选项卡上的"内存使用情况"
原因是,一旦启用内存中的锁定页面,SQL Server 就会开始使用AWE API,并且不会通过 OS 虚拟内存管理器分配内存。锁定这些页后,这些性能计数器表示 SQL Server 进程中不使用缓冲池的分配的内存分配。SQL Server:内存管理器性能对象的总服务器内存 (KB) 计数器仍然准确表示为缓冲池分配的内存。

为 SQL Server 实例分配"在内存中锁定页面"用户权利之前的重要注意事项

在将锁定页分配给内存用户右侧之前,应进行其他注意事项。如果在配置错误的系统上分配此用户的权利,则系统可能会变得不稳定或整个系统的性能下降。此外,事件 ID 333 可能会记录在事件日志中。如果您与 Microsoft 客户支持服务 (CSS) 联系,则 CSS 工程师可能会要求您撤销此用户对用作 SQL Server 服务启动帐户的用户帐户的权限。此步骤可能需要用于收集 CSS 工程师可用于必要配置 SQL Server 和在系统上运行的其他应用程序的各种选项的重要性能数据。CSS 工程师收集性能数据后,可以将内存中的锁定页直接分配给 SQL Server 服务的启动帐户。在正确分配内存用户的Lock 页之前,请确保捕获性能监视器日志以确定系统上安装的各种应用程序和服务的内存要求。这些应用程序还包括 SQL Server 。要确定内存要求,请收集以下基线信息:
  • 请确保正确设置最大服务器内存选项和最小服务器内存选项。这些选项仅反映 SQL Server 进程缓冲池的内存要求。这些选项不包括为 SQL Server 进程中的其他组件分配的内存。这些组件包括:
    • SQL 服务器工作线程
    • SQL Server 进程在 SQL Server 进程的地址空间内加载的各种 DLL 和组件
    • 备份和恢复操作
  • DLL 和组件包括各种 OLE DB 提供程序、扩展存储过程、用于sp_OACreate存储过程的 Microsoft COM 对象、链接的服务器和 SQL Server CLR。为这些组件分配的内存位于 SQL Server 进程地址空间的非缓冲池区域之下。要理想地确定整个 SQL Server 进程可以使用的最大内存量,必须从希望 SQL Server 进程使用的总内存中减去为不使用缓冲池的组件分配的内存。然后,可以使用剩余值来设置最大服务器内存选项。在设置最大服务器内存选项和最小服务器内存选项之前,应仔细查看 SQL 服务器联机丛书中的"手动设置内存选项"主题。
  • 确定其他应用程序和 Windows 操作系统组件的内存要求。应用程序可能包括其他 SQL Server 组件,例如 SQL Server 代理、SQL Server 复制代理、SQL Server 报告服务、SQL Server 分析服务、SQL Server 集成服务和 SQL Server 全文搜索。执行备份操作和文件复制操作的应用程序可能会使用大量内存。请考虑生成文件 IO 的操作,如批量复制和快照代理。在确定最大服务器内存选项和最小服务器内存选项的值时,必须考虑所有这些应用程序的内存要求。您可以为每个进程对象使用"专用字节"计数器和 Process 对象下的"工作集"计数器来确定特定进程的内存要求。
  • 默认情况下,内存用户右侧的锁定页已分配给内置的本地系统帐户。有关详细信息,请访问以下 Microsoft 网站:
  • 如果对域中的所有 SQL Server 进程全局使用 Windows 用户帐户,请确定使用组策略配置分配的用户权限。32 位 SQL Server 进程可能使用此帐户作为启动帐户。但是,此帐户需要内存用户权利的锁定页才能启用地址窗口扩展 (AWE) 功能。有关详细信息,请参阅 SQL 服务器联机丛书中的"向 SQL Server 提供最大内存量"主题。
  • 在为多个 SQL Server 实例配置最大服务器内存选项和最小服务器内存选项之前,请考虑 SQL Server 的每个实例的非缓冲池的内存要求。然后,为每个 SQL Server 实例配置这些选项。

理想情况下,在峰值负载期间收集此基线信息。因此,您可以确定各种应用程序和组件的内存要求,以支持峰值负载。内存要求因系统而异,具体取决于在系统上运行的活动和应用程序。

您可以查询动态管理视图 sys.dm_os_process_memory 中提供的信息,以了解系统是否遇到内存不足的情况。有关详细信息,请参阅以下站点的 SQL 服务器联机联机参考:

sys.dm_os_process_memory(转换-SQL)

在 Windows 服务器 2008 和 Windows 服务器 2008 R2 中添加的改进,减少了工作集修剪

Windows 服务器 2008 和 Windows 服务器 2008 R2 改进了连续内存分配机制。此改进使 Windows Server 2008 和 Windows Server 2008 R2 在一定程度上减少了当新内存请求到达时分页应用程序工作集的影响。以下是微软白皮书"Windows 内存管理的进步"的改进说明:
在 Windows Server 2008 中,物理连续内存的分配大大增强。分配连续内存的请求更有可能成功,因为内存管理器现在动态替换页面,通常无需修剪工作集或执行 I/O 操作。此外,还有更多类型的页面(如内核堆栈和文件系统元数据页等)现在都是可供替换的候选页面。因此,在任何给定时间通常都提供更多的连续内存。此外,获得这种拨款的成本也大大降低。
要查看"Windows 内存管理方面的改进"白皮书,请访问以下 Microsoft 网站:有关详细信息,请访问以下 Microsoft 网站:
2001745工作集修整可能会对 Windows 2003 下的 SQL、Exchange 和操作系统性能产生负面影响
备注在基于 Windows Server 2008 的计算机上,必须安装 SQL Server 服务包 2 (SP2) 或更高版本的 SQL Server 服务包。本文讨论的第三方产品由独立于 Microsoft 的公司制造。Microsoft 对这些产品的性能或可靠性不作任何暗示或其他的担保。