主页 / dba / 问题 / 204096
Accepted
PicoDeGallo
Asked: 2018-04-17 07:51:31 +0800 CST

SQL Server 的“总服务器内存”消耗数月停滞不前，但可用容量超过 64GB

  • 772

我遇到了一个奇怪的问题，SQL Server 2016 标准版 64 位似乎已经将自己限制在分配给它的总内存的一半（64GB 的 128GB）。

的输出@@VERSION是：

Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64) 2017 年 12 月 22 日 11:25:00 版权所有 (c) Microsoft Corporation Standard Edition (64-bit) o​​n Windows Server 2012 R2 Datacenter 6.3 (内部版本 9600：）（管理程序）

的输出sys.dm_os_process_memory是：

sys.dm_os_process_memory

当我查询时sys.dm_os_performance_counters，我看到Target Server Memory (KB)是 at131072000并且Total Server Memory (KB)是 at 的一半以下65308016。在大多数情况下，我认为这是正常行为，因为 SQL Server 尚未确定它需要为自己分配更多内存。

但是，它已经“卡在”~64GB 两个多月了。在此期间，我们对一些数据库执行了大量内存密集型操作，并向实例添加了近 40 个数据库。我们总共有 292 个数据库，每个数据库的预分配数据文件为 4GB，自动增长速度为 256MB，日志文件为 2GB，自动增长速度为 128MB。我每晚在上午 12:00 执行一次完整备份，并从周一到周五从上午 6:00 到晚上 8:00 开始每隔 15 分钟进行一次事务日志备份。这些数据库的整体吞吐量相对较低，但我怀疑有些事情是错误的，因为 SQL Server 还没有爬上Target Server Memory自然地通过新的数据库添加、正常的查询执行以及已经运行的内存密集型 ETL 管道。

SQL Server 实例本身位于一个虚拟化 (VMware) Windows Server 2012R2 服务器之上，该服务器具有 12 个 CPU、144GB 内存（128GB 到 SQL Server，16GB 为 Windows 保留）和 4 个虚拟磁盘，这些虚拟磁盘位于具有 15K SAS 驱动器的 vSAN 之上. Windows 自然位于 64GB C: 磁盘上，页面文件为 32GB。数据文件位于 2TB D: 磁盘上，日志文件位于 2TB L: 磁盘之上，而 tempdb 位于 256GB T: 磁盘上，其中包含 8x16GB 文件，没有自动增长。

我已经验证除了MSSQLSERVER.

服务

此服务器完全专用于 SQL Server 实例，因此我们没有在其上运行可能会消耗内存的其他应用程序或服务。

资源监视器

我使用 RedGate SQL Monitor 进行分析，下面是过去 18 天的Total Server Memory. 如您所见，除了 4 月初单次上升约 300MB 之外，内存利用率一直完全停滞不前。

RedGate SQL 监视器

这可能是什么原因？为了确定为什么 SQL Server 不想使用分配给它的额外 64GB+ 内存，我可以仔细查看什么？

运行的输出sp_Blitz：

sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1;

优先级 50：性能：

  • CPU 调度程序脱机 - 由于关联屏蔽或许可问题，SQL Server 无法访问某些 CPU 内核。

  • 内存节点脱机 - 由于关联屏蔽或许可问题，某些内存可能不可用。

优先级 50：可靠性：

  • 远程 DAC 已禁用 - 未启用对专用管理员连接 (DAC) 的远程访问。当 SQL Server 无响应时，DAC 可以更轻松地进行远程故障排除。

优先级 100：性能：

  • 一个查询的许多计划 - 计划缓存中的单个查询存在 300 个计划 - 这意味着我们可能存在参数化问题。

  • 启用服务器触发器

    • 服务器触发器 [RG_SQLLighthouse_DDLTrigger] 已启用。确保您了解触发器在做什么——它做的工作越少越好。

    • 服务器触发器 [SSMSRemoteBlock] 已启用。确保您了解触发器在做什么——它做的工作越少越好。

优先级 150：性能：

  • 强制加入提示的查询 - 自重启以来已记录了 1480 个加入提示实例。这意味着查询支配着 SQL Server 优化器，如果他们不知道自己在做什么，这可能弊大于利。这也可以解释为什么 DBA 调优工作不起作用。

  • 查询强制订单提示 - 自重启以来已记录 2153 个订单提示实例。这意味着查询支配着 SQL Server 优化器，如果他们不知道自己在做什么，这可能弊大于利。这也可以解释为什么 DBA 调优工作不起作用。

优先级 170：文件配置：

  • C盘系统数据库

    • master - master 数据库在 C 驱动器上有一个文件。将系统数据库放在 C 驱动器上可能会在服务器空间不足时导致服务器崩溃。

    • 模型 - 模型数据库在 C 驱动器上有一个文件。将系统数据库放在 C 驱动器上可能会在服务器空间不足时导致服务器崩溃。

    • msdb - msdb 数据库在 C 驱动器上有一个文件。将系统数据库放在 C 驱动器上可能会在服务器空间不足时导致服务器崩溃。

优先级 200：信息：

  • 代理作业同时启动 - 多个 SQL Server 代理作业配置为同时启动。有关详细的时间表列表，请参阅 URL 中的查询。

  • Master 数据库 master 中的表 - master 数据库中的 CommandLog 表由最终用户于 2017 年 7 月 30 日下午 5:22 创建。发生灾难时，主数据库中的表可能无法恢复。

  • TraceFlag 开启

    • 跟踪标志 1118 全局启用。

    • 跟踪标志 1222 全局启用。

    • 跟踪标志 2371 已全局启用。

优先级 200：非默认服务器配置：

  • 代理 XP - 此 sp_configure 选项已更改。它的默认值为 0，并已设置为 1。

  • 备份校验和默认值 - 此 sp_configure 选项已更改。它的默认值为 0，并已设置为 1。

  • 备份压缩默认值 - 此 sp_configure 选项已更改。它的默认值为 0，并已设置为 1。

  • 并行度的成本阈值 - 此 sp_configure 选项已更改。它的默认值为 5，已设置为 48。

  • max degree of parallelism - 此 sp_configure 选项已更改。它的默认值为 0，已设置为 12。

  • 最大服务器内存 (MB) - 此 sp_configure 选项已更改。其默认值为 2147483647，已设置为 128000。

  • 针对临时工作负载进行优化 - 此 sp_configure 选项已更改。它的默认值为 0，并已设置为 1。

  • 显示高级选项 - 此 sp_configure 选项已更改。它的默认值为 0，并已设置为 1。

  • xp_cmdshell - 此 sp_configure 选项已更改。它的默认值为 0，并已设置为 1。

优先级 200：可靠性：

  • Master中的扩展存储过程

  • master - [sqbdata] 扩展存储过程位于 master 数据库中。CLR 可能正在使用中，并且主数据库现在需要成为您的备份/恢复计划的一部分。

    • master - [sqbdir] 扩展存储过程位于 master 数据库中。CLR 可能正在使用中，并且主数据库现在需要成为您的备份/恢复计划的一部分。

    • master - [sqbmemory] ​​扩展存储过程位于 master 数据库中。CLR 可能正在使用中，并且主数据库现在需要成为您的备份/恢复计划的一部分。

    • master - [sqbstatus] 扩展存储过程位于 master 数据库中。CLR 可能正在使用中，并且主数据库现在需要成为您的备份/恢复计划的一部分。

    • master - [sqbtest] 扩展存储过程位于 master 数据库中。CLR 可能正在使用中，并且主数据库现在需要成为您的备份/恢复计划的一部分。

    • master - [sqbtestcancel] 扩展存储过程位于 master 数据库中。CLR 可能正在使用中，并且主数据库现在需要成为您的备份/恢复计划的一部分。

    • master - [sqbteststatus] 扩展存储过程位于 master 数据库中。CLR 可能正在使用中，并且主数据库现在需要成为您的备份/恢复计划的一部分。

    • master - [sqbutility] 扩展存储过程位于 master 数据库中。CLR 可能正在使用中，并且主数据库现在需要成为您的备份/恢复计划的一部分。

    • master - [sqlbackup] 扩展存储过程位于 master 数据库中。CLR 可能正在使用中，并且主数据库现在需要成为您的备份/恢复计划的一部分。

优先级 210：非默认数据库配置：

  • Read Committed Snapshot Isolation Enabled - 此数据库设置不是默认设置。

    • 红门

    • 红门监视器

  • 启用快照隔离 - 此数据库设置不是默认设置。

    • 红门

    • 红门监视器

优先级 240：等待统计：

  • 1 - SOS_SCHEDULER_YIELD - 1770.8 小时等待，115.9 分钟平均每小时等待时间，100.0% 信号等待，1419212079 个等待任务，4.5 毫秒平均等待时间。

优先级 250：信息：

  • SQL Server 在 NT 服务帐户下运行 - 我作为 NT Service\MSSQLSERVER 运行。我希望我有一个 Active Directory 服务帐户。

优先级 250：服务器信息：

  • 默认跟踪内容 - 默认跟踪包含 2018 年 4 月 14 日晚上 11:21 到 2018 年 4 月 16 日上午 11:13 之间的 36 小时数据。默认跟踪文件位于：C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log

  • 驱动器 C 空间 - C 驱动器上有 196816.00MB 可用空间

  • 驱动器 D 空间 - E 驱动器上有 894823.00MB 可用空间

  • 驱动器 L 空间 - F 驱动器上有 1361367.00MB 可用空间

  • 驱动器 T 空间 - G 驱动器上有 114441.00MB 可用空间

  • 硬件 - 逻辑处理器：12 个。物理内存：144GB。

  • 硬件 - NUMA 配置

    • 节点：0 状态：ONLINE 在线调度程序：4 离线调度程序：2 处理器组：0 内存节点：0 内存 VAS 保留 GB：186

    • 节点：1 状态：OFFLINE 在线调度程序：0 离线调度程序：6 处理器组：0 内存节点：0 内存 VAS 保留 GB：186

  • 已启用即时文件初始化 - 服务帐户具有执行卷维护任务权限。

  • 电源计划 - 你的服务器有 2.60GHz 的 CPU，并且处于平衡电源模式——呃......你希望你的 CPU 全速运行，对吗？

  • 服务器上次重启 - 2018 年 3 月 9 日上午 7:27

  • 服务器名称 - [已编辑]

  • 服务

    • 服务：SQL Server (MSSQLSERVER) 在服务帐户 NT Service\MSSQLSERVER 下运行。上次启动时间：2018 年 3 月 9 日上午 7:27。启动类型：自动，当前正在运行。

    • 服务：SQL Server 代理 (MSSQLSERVER) 在服务帐户 LocalSystem 下运行。上次启动时间：未显示。启动类型：自动，当前正在运行。

  • SQL Server 上次重新启动 - 2018 年 3 月 9 日上午 6:27

  • SQL Server 服务 - 版本：13.0.4466.4。补丁级别：SP1。累积更新：CU7。版本：标准版（64 位）。启用的可用性组：0。可用性组管理器状态：2

  • 虚拟服务器 - 类型：（HYPERVISOR）

  • Windows 版本 - 您正在运行一个非常现代的 Windows 版本：Server 2012R2 时代，版本 6.3

优先级 254：运行日期：

  • 船长的日志：给某事和某事约会……
sql-server performance

3 个回答

  1. Best Answer

    我敢打赌，您已经以某些 CPU 节点和/或内存节点处于脱机状态的方式配置了虚拟 CPU。

    下载sp_Blitz（免责声明：我是该免费开源脚本的作者之一）并运行它：

    sp_Blitz @CheckServerInfo = 1;

    查找有关 CPU 和/或内存节点脱机的警告。SQL Server 标准版只看到前 4 个 CPU 插槽，您可能已将 VM 配置为 6 个双核 CPU。它最终会遇到一个类似于Enterprise Edition 的 20-core-limits 如何限制您可以看到的内存量的问题。

    如果您想在此处分享 sp_Blitz 的输出，您可以像这样运行它以输出到 Markdown，然后您可以将其复制/粘贴到您的问题中：

    sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1;

    更新 2018/04/16 - 确认。您附加了 sp_Blitz 输出（谢谢！），它确实表明您的 CPU 和内存节点处于脱机状态。构建 VM 的人将其配置为 12 个单核 CPU，因此 SQL Server 标准版只能看到前 4 个插槽（核心）以及连接到它们的内存。

    要修复它，请关闭 VM，将其配置为 2 插槽、6 核 VM，然后 SQL Server 标准版将看到所有内核和内存。这也将减少您的 SOS_SCHEDULER_YIELD 等待 - 现在，您的 SQL Server 正在敲击前 4 个内核，但仅此而已。在此修复之后，它将能够在所有 12 个内核上工作。

    • 54

  2. 作为Brent Ozar 行动计划的附录，我想分享结果。正如 Brent 所指出的，在 VMware 中，我们错误地为虚拟机配置了 12 个单核 CPU。这导致 SQL Server 无法访问剩余的 8 个内核，从而导致我原来的问题中描述的内存问题。我们昨晚将服务置于维护模式，以便适当地重新配置 VM。我们不仅看到内存以正常方式增加，而且正如 Brent 所暗示的那样，等待的数量呈指数级下降，我们的整体 SQL Server 性能飙升。vNUMA 配置现在是快乐的小组件，可以分割我们的工作负载。

    对于可能使用 VMware vSphere 6.5 的用户，完成 Brent 描述的操作项的简要步骤如下。

    1. 登录到您的 VMware 集群的 vSphere Web Client，然后浏览到托管 SQL Server 的虚拟机。您的 VM 必须处于脱机状态才能调整 CPU 和内存配置。

    2. 在主窗格中，转到Configure > VM hardware，单击右上角的Edit按钮。您将打开一个上下文菜单，其中包含Edit Settings. 作为参考，下图是不正确的配置。请注意，我已Cores per Socket设置为1. 鉴于 SQL Server 标准版的限制，这是一个糟糕的配置。

      不正确的配置

    3. 修复就像调整Cores per Socket值一样简单。在我们的例子中，我们将它设置为，6以便我们拥有2 Sockets. 这允许 SQL Server 使用所有 12 个处理器。

      正确配置

    重要说明：请勿将值设置为 theNumber of Cores或 theSockets为奇数的位置。NUMA 喜欢平衡，根据经验，它需要被 2 整除。例如，4 核到 3 插槽的配置会不平衡。事实上，如果你sp_Blitz使用这种类型的配置运行，它会抛出一个警告。

    在 VMware vSphere 上构建 Microsoft SQL Server （PDF 警告）中的第 3.3 节详细概述了这一点。白皮书中概述的做法适用于 SQL Server 的大多数本地虚拟化。

    以下是我在布伦特的帖子之后通过我的研究收集的更多资源：

    我将以过去 24 小时从 RedGate SQL Monitor 捕获的内容结束。需要注意的主要一点是 CPU 利用率和等待次数 - 在昨天的高峰时段，我们遇到了 CPU 使用率和等待争用情况。在这个简单的修复之后，我们的性能提高了十倍。甚至我们的磁盘 I/O 也大大减少了。这是一个看似容易被忽视的设置，可以将虚拟性能提高一个数量级。至少，它被我们的工程师忽略了，而且是一个完整的d'oh时刻。

    RedGatePerf

    • 8

  3. 此外，根据MSDN，SQL Server 标准仅限于 64GB 的 RAM。我们通过将数据库拆分为多个实例来“解决”这个问题，但您的情况可能不允许这样做。

    嗯 2016 似乎有 128GB 作为限制，但实例拆分可能仍然是一个选项。

    • -1

相关问题