我遇到了一个奇怪的问题：SQL Server 2016 Standard Edition 64位似乎已经限制了为其分配的总内存的正好一半（64GB或128GB）。

@@VERSION的输出是：


Microsoft SQL Server 2016（SP1-CU7-GDR）（KB4057119）-13.0.4466.4（X64）2017年12月22日11:25:00 Windows Server 2012 R2 Datacenter 6.3（Build 9600：）（Hypervisor）上的（c）Microsoft Corporation标准版（64位）版权所有（c）管理程序


sys.dm_os_process_memory的输出为：



查询sys.dm_os_performance_counters时，我看到Target Server Memory (KB)在131072000处，而Total Server Memory (KB)在65308016处的一半以下。在大多数情况下，我会认为这是正常现象，因为SQL Server尚未确定它需要为其自身分配更多的内存。

，它已经“卡住”了〜64GB现在超过2个月了。在这段时间内，我们对某些数据库执行了大量内存密集型操作，并向实例添加了近40个数据库。我们共有292个数据库，每个数据库都有4GB的预分配数据文件（自动增长速率为256MB）和2GB的日志文件的自动增长速率为128MB。我每晚晚上12:00执行一次完整备份，并从星期一到星期五从6:00 AM到8:00 PM（每15分钟间隔）开始事务日志备份。这些数据库的整体吞吐量相对较低，但是我怀疑是什么问题，因为SQL Server并没有自然地通过添加新数据库，正常查询执行以及占用大量内存的ETL管道自然而步入Target Server Memory已经运行了。

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实例正在运行。仅SQL Server实例，因此我们没有在其上运行的其他任何可能消耗内存的应用程序或服务。



我使用RedGate SQL Monitor进行分析，以下是过去18天Total Server Memory的历史。如您所见，除了4月初的300MB内存增长外，内存利用率仍然完全停滞。为了确定为什么SQL Server不想使用为其分配的额外64GB +内存，我可以仔细研究一下吗？
/> 
sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1;


优先级50：性能：


CPU调度程序脱机-由于亲缘关系屏蔽或许可问题。
内存节点脱机-由于相似性屏蔽或许可问题，某些存储器可能不可用。

优先级50：可靠性：


禁用远程DAC-未启用对专用管理连接（DAC）的远程访问。当SQL Server没有响应时，DAC可以使远程故障排除变得更加容易。

优先级100：性能：


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

已启用服务器触发器触发器[RG_SQLLighthouse_DDLTrigger]已启用。确保了解该触发器的作用-所做的工作越少，就更好。
服务器触发器[SSMSRemoteBlock]已启用。确保您了解触发器的作用-所做的工作越少，
就越好。




优先级150：性能：


查询强制连接提示-自重新启动以来，已记录1480个连接提示实例。这意味着查询使SQL
服务器优化器处于主导地位，并且如果它们不知道自己在做什么，这可能造成弊大于利。这也可以解释为什么DBA
调整工作不起作用。
查询强制执行订单提示-自重新启动以来已记录2153个订单提示实例。这意味着查询正在使SQL
服务器优化器处于主导地位，并且如果它们不知道自己在做什么，这可能造成弊大于利。这也可以解释为什么DBA
调整工作不起作用。

优先级170：文件配置：



系统数据库已打开C驱动器


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



优先级200：信息性：


代理作业同时启动-将多个SQL Server代理作业配置为同时启动。有关详细的日程表
列表，请参阅URL中的查询。
主数据库主数据库中的表-主数据库中的CommandLog表由最终用户于2017年7月30日5:22 PM创建。 >如果发生灾难，可能无法还原master数据库中的表。

TraceFlag On


全局启用了跟踪标志1118。
全局启用跟踪标志1222。
全局启用跟踪标志2371。




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


代理XPs-此sp_configure选项已更改。其默认值为0，并且已设置为1。
备份校验和默认值-此sp_configure选项已更改。其默认值为0，并且已设置为1。
备份压缩默认值-此sp_configure选项已更改。其默认值为0，并且已将其设置为1。
并行性的成本阈值-此sp_configure选项已更改。其默认值是5，并且已将其设置为48。其默认值为0，并且已将其设置为12。
最大服务器内存（MB）-此sp_configure选项已更改。其默认值为2147483647，并且已将其设置为128000。
针对临时工作负载进行优化-此sp_configure选项已更改。其默认值为0，并且已将其设置为1。
show advanced options-此sp_configure选项已更改。其默认值为0，并且已将其设置为1。
xp_cmdshell-此sp_configure选项已更改。其默认值为0，并且已将其设置为1。

优先级200：可靠性：


主数据库中扩展的存储过程

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


master-[sqbdir]扩展存储该过程位于master
数据库中。可能正在使用CLR，现在必须将master数据库作为备份/恢复计划的一部分。
master-[sqbmemory]扩展存储过程位于master
数据库中。可能正在使用CLR，现在需要master数据库成为备份/恢复计划的一部分。
master-[sqbstatus]扩展的存储过程位于master
数据库中。可能正在使用CLR，现在master数据库已成为备份/恢复计划的一部分。
master-[sqbtest]扩展存储过程位于master
数据库中。可能正在使用CLR，现在必须将master数据库作为备份/恢复计划的一部分。
master-[sqbtestcancel]扩展存储过程位于master数据库中。 CLR可能正在使用中，现在master数据库需要
作为备份/恢复计划的一部分。
master-[sqbteststatus]扩展存储过程位于master数据库中。可能正在使用CLR，现在master数据库需要
作为备份/恢复计划的一部分。
master-[sqbutility]扩展存储过程在master数据库中。可能正在使用CLR，现在master数据库需要
作为备份/恢复计划的一部分。
master-[sqlbackup]扩展存储过程位于master
数据库中。 CLR可能正在使用中，现在需要将master数据库作为备份/恢复计划的一部分。




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



启用已提交的快照隔离-此数据库设置不是默认数据库设置。


RedGate
RedGateMonitor



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


RedGate
RedGateMonitor



优先级240：等待统计：


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

优先级250：信息性：


SQL Server在NT服务下运行帐户-我以NT Service \ MSSQLSERVER的身份运行。我希望我改用Active Directory服务
帐户。

优先级250：服务器信息：


默认跟踪内容-默认跟踪保存36在2018年4月14日11:21 PM和2018年4月16日11:13 AM之间的小时数据。默认的trace
文件位于：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 Config


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


>启用即时文件初始化-服务帐户具有“执行卷维护任务”权限。
电源计划-您的服务器具有2.60GHz CPU，并且处于平衡电源模式下-呃...您希望您的CPU运行在全速吧？
服务r上一次重新启动-2018年3月9日7:27 AM
服务器名称-[已编辑]

服务


服务：SQL Server（MSSQLSERVER）在服务帐户NT Service \ MSSQLSERVER下运行。上次启动时间：2018年3月9日上午7:27。启动
类型：自动，当前正在运行。
服务：SQL Server代理（MSSQLSERVER）在服务帐户LocalSystem下运行。上次启动时间：未显示。启动类型：自动，
当前正在运行。


SQL Server上次重新启动-2018年3月9日6:27 AM
SQL Server服务-版本：13.0.4466.4。补丁程序级别：SP1。累积更新：CU7。版本：标准版（64位）。
可用性组已启用：0。可用性组管理器状态：2
虚拟服务器-类型：（HYPERVISOR）
Windows版本-您正在运行Windows的非常现代的版本：Server 2012R2时代，版本6.3

优先级254：运行日期：


船长的日志：为某事加上某事的日期...



评论

此答案可能有助于SQL Server不使用所有内存
请添加select @@ version的完整输出，然后从sys.dm_os_process_memory中选择*。您是否尝试过查看perfmon计数器的总服务器内存（KB）值？
@SqlWorldWide我已经问过这个问题了，给出的建议实际上就是我在主要主题中提供的建议。我无法通过该帖子找到针对我给定场景的解决方案。
@Shanky我已经添加了请求的输出。服务器总内存（KB）由sys.dm_os_performance_counters提供。

#1 楼

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

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

sp_Blitz @CheckServerInfo = 1;


查找有关CPU和/或内存节点脱机的警告。 SQL Server Standard Edition仅显示前4个CPU插槽，并且您可能已将VM配置为6个双核CPU。最终将遇到一个问题，类似于企业版的20核限制如何限制您可以看到的内存量。

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

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

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

要修复它，请关闭VM，将其配置为2插槽，6核VM，然后SQL Server Standard Edition将看到所有核和内存。这也将减少您的SOS_SCHEDULER_YIELD等待-现在，您的SQL Server锤击了前4个核心，仅此而已。修复之后，它将可以在所有12个内核上运行。

评论

不同的页面，我想相同的视频

–玛丽安
18年4月16日在17:46
@BrentOzar我在这里分享了此配置更改之前/之后的结果。感谢您的协助-您为我们省去了很多麻烦！

– PicoDeGallo
18年4月17日在16:55
@PicoDeGallo不客气！是的，这就是为什么我将它放在sp_Blitz中-我们发现了许多此类常见问题，仅通过运行免费的健康检查程序就可以轻松解决这些问题。顺便说一句，爱你的莎莎酱。 （等等，这听起来是错误的。）

–布伦特·奥扎（Brent Ozar）
18年4月17日在17:48

#2 楼

作为布伦特·奥扎尔（Brent Ozar）行动计划的补充，我想分享结果。正如布伦特（Brent）所指出的，在VMware中，我们为虚拟机配置了不正确的12个单核CPU。这导致SQL Server无法访问其余8个内核，结果导致了我最初的问题中描述的内存问题。昨晚我们将服务置于维护模式，以便适当地重新配置VM。我们不仅看到内存以正常的方式增长，而且正如Brent所暗示的那样，等待的次数呈指数下降，并且我们的整体SQL Server性能急剧上升。现在，vNUMA配置是满足我们工作负载需求的小型组件。

对于那些可能正在使用VMware vSphere 6.5的组件，完成Brent描述的操作项的简要步骤如下。 />登录到VMware群集的vSphere Web Client，然后浏览到承载SQL Server的虚拟机。您的VM必须处于脱机状态才能调整CPU和内存配置。

在主窗格中，转到Configure > VM hardware，单击右上角的Edit按钮。您将打开一个具有Edit Settings的上下文菜单。供参考，下图是错误的配置。请注意，我将Cores per Socket设置为1。鉴于SQL Server Standard Edition的局限性，这是一个错误的配置。在我们的情况下，将其设置为Cores per Socket，这样我们就有6。这使SQL Server可以利用所有12个处理器。




重要说明：请勿将2 Sockets或Number of Cores的值设置为奇数。 NUMA喜欢平衡，根据经验，它需要被2整除。例如，将4个核配置为3个插槽的配置将是不平衡的。实际上，如果您要使用这种类型的配置运行Sockets，则会发出有关此警告。这个详细。白皮书中概述的实践适用于大多数SQL Server本地虚拟化。 >
虚拟化大型数据库-VMware CPU容量规划
虚拟机vCPU和vNUMA权限调整–经验法则
vSphere 6.5中每个套接字的核心与虚拟NUMA拓扑解耦


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



评论

+1确实完成了Brent Ozar的回答。

– Shanky
18年4月18日在6:57

#3 楼

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