我们有
Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )
服务器物理上安装了 256 GB 的 RAM。SQL Server 设置为 210 GB 的内存限制。我注意到服务器在进行查询时每个查询请求 39 GB 的内存 (210 * 0.75 * 0.25),即它认为它有 210 GB 可用,而不是 128 GB。
问题 - 此设置是否正确或是否应根据 SQL Server 标准版 - 128 GB 设置限制?
首先,让我们谈谈 SQL Server 标准版的 128 GB 内存限制。正如 Erik Darling 在评论中提到的,此标准版限制仅适用于缓冲池/数据库缓存。SQL Server 有大量其他内存使用,包括列存储对象池和使用的排序/哈希查询内存,它们都属于被盗内存类别,因此不受数据库缓存限制的限制。
以下每个值都将相等(缓冲池增长/收缩期间的轻微计时问题除外),因此可以检查这些位置中的任何一个以查看数据库缓存的当前大小。
SQL Server 不会“实际检查”Max Server Memory 配置的值 - UI 不会拒绝任何值,除非它是数字溢出,SQL Server 错误日志中不会有任何消息指示value 是由于任何原因在给定系统上无法实现的值。
如果 SQL Server 决定它可以在任何给定时间实现/维持 Max Server Memory 值 - 给定系统 RAM 和当时的内存使用情况 - 它将使用该值作为目标服务器内存 (KB)。否则它将选择一个较低的、可实现的值。SQL Server 侦听来自操作系统的内存不足通知,如果发生内存不足通知并且 SQL Server 可以释放一些内存,它就会释放。为了做一个好邻居。
Total Server Memory (KB) 是数据库缓存、空闲 SQLOS 内存和被盗内存(除了数据库缓存之外的所有内容,包括计划缓存、锁内存、列存储等)的总和。
目标(无论如何我的目标)是保持总服务器内存 = 目标服务器内存 = 最大服务器内存。这并不总是可能的,尤其是在批处理模式执行期间,激进的内存分配导致总服务器内存超过目标服务器内存。反正...
在标准版中,当前无法访问资源调控器。因此,用户查询每个查询的最大内存授予不可配置,为 25%。但是... 25% 什么?
“什么”是相关资源信号量的目标内存。每个资源调控器资源池都有两个用于查询内存的资源信号量——一个普通的和一个小的。在标准版中,由于无法访问资源调控器,所有用户查询都将转到同一组资源信号量(基本上是默认池的信号量)。在这里,由于我们谈论的是大内存授予,所以我们谈论的是常规资源信号量。
这是一种检查资源信号量操作的方法。这个查询很幸运,成为当时系统上唯一运行的东西。它在默认资源池中,它非常小,以至于进入小信号量而不是常规信号量。所以这个查询是唯一的被授权者,它获得了 1136 kb 的授权,在报告时它正在使用 176 kb。如果一个查询计划有一个非常非常高的内存估计,它可能会最大化内存授予 - 在这种情况下,它可以要求的最多是它所在池的常规信号量的 25%。(或者在标准版中,只有池可用于用户查询:-))
常规资源信号量的目标不是单一的固定值——它是自适应的,就像其他 SQL Server 内存目标一样。内部资源池中的大量查询内存活动(例如针对非常大的表的多个异步自动统计更新)将导致默认池常规信号量的目标减少。如果列存储对象池突然变得非常大,而牺牲了 SQL Server Total Server Memory 中的其他用途,则资源池的常规信号量目标可能会减少。在这种情况下,25% 的限制也会降低——不是因为分子发生了变化,而是因为分母发生了变化。
您可以从性能计数器跟踪这些详细信息,例如本段下方的查询。需要注意的一个细节:最大工作空间内存 (KB) 仅适用于默认池 - 在标准版中,它是用户查询的唯一机会,但在可能已添加其他用户池的企业版中记住这一点很重要。最大工作空间内存 (KB) 是小型和常规信号量目标的总和。如果每个查询限制有 25% 的工作空间,则最大工作空间内存 (KB) 计数器的 25% 将仅比实际限制高一点点,因为它包含了小信号量目标。
内存授予的大小可能是一个持续的挑战。我的经验是,SQL Server 内存估计与实际运行时实际的差异比行数估计与实际行之间的差异大几倍。理想的内存估计 - 在考虑每个查询工作区内存最大值之前的估计 - 几乎总是显着高于所需的(我,你的里程可能会有所不同)。这是有道理的,如果单独运行的查询的性能是主要范例。例如,当像我这样的人出现想要让系统达到非常高的 cpu 利用率时,只要有工作要做,就可以做有意义的工作:-) 我使用的大多数系统都无法接近在不使用资源调控器调整每个查询的最大内存授予的情况下,它们的潜力。
最后,让我们谈谈大内存授予。非常大的内存授予不一定表示查询编写不当或需要更改架构、更新统计信息等。有时它只是 - 就像基数估计或查询优化的其他限制一样。
图表A:dbcc checkdb,带有逻辑检查(即没有physical_only 选项)。随着表的增长,内存估计、理想内存和授权请求也会增长。直到他们达到极限。除了使用资源管理器调低它之外,没有什么可做的。
图表 B:批量插入到聚集列存储索引中。SQL Server 使用基于行组质量预测范围的内存估计范围,试图平衡使用的资源、性能和最终结果。有时甚至 CCI 批量插入的范围的下限也比查询使用的要多得多。没什么可做的。事实上,尝试过多地“调整”最大授权,将其降低得太接近插入查询实际使用的值,可能最终会降低请求 DOP 的 DOP,或者更糟糕的是......让 SQL Server 放弃批量插入,而不是去涓流插入和使用增量存储。
所以……你去吧。检查 Standard Edition SQL Server 实例中的目标服务器内存 (KB) 和总服务器内存 (KB)。它们不受 128 GB 限制的直接约束。您可以检查数据库缓存大小以验证它是否受 128 GB 限制的约束。您可以检查常规资源信号量目标以查看 25% 限制的计算依据。
您有一个标准版本的 SQL Server,其缓冲池限制为 128 GB,但这只是缓冲池限制。如果您使用内存中 OLTP,则每个实例的列存储段缓存的最大内存限制为 32 GB,而每个数据库的内存优化数据大小的最大内存限制为 32 GB。这些内存将不受缓冲池控制,而是由最大服务器内存设置控制。有关更多详细信息,请阅读SQL Server 2016 SP1:内存限制。从链接
因此,如果您大量使用内存优化表和内存中 OLTP,则最大服务器内存值将超过 128 GB,以容纳内存中功能所需的额外内存。