我正在尝试解决我们在生产 SQL Server 上看到的一些间歇性 CPU 峰值。我们正在运行具有 28 GB RAM 和 4 个 CPU 内核的 SQL Server 2008 R2 标准版。发生这种情况时,我们注意到大量的 RESOURCE_SEMAPHORE_QUERY_COMPILER 等待,持续大约一两分钟然后停止,然后 CPU 使用率恢复正常。
经过研究,我了解到这通常是由编译大量不可重用的执行计划引起的,我们目前正在对我们的应用程序进行更改以解决这些问题。
由于内存压力,计划缓存驱逐也会触发此行为吗?如果是这样,我将如何检查这个?我正在尝试查看是否有任何短期补救措施,例如升级服务器 RAM,直到我们部署应用程序修复程序。我能想到的唯一其他短期选择是将一些最繁忙的数据库移动到不同的服务器上。
我相信如果您有很多大型查询计划正在争夺内存以进行编译(这与运行查询本身几乎没有关系),我相信您会看到这种症状。为了解决这个问题,我怀疑您使用的是 ORM 或某种应用程序,它们会生成许多独特但相对复杂的查询。由于大型查询操作等原因,SQL Server 可能会承受内存压力,但进一步考虑,更有可能只是您的系统配置的内存远远少于它需要的内存(要么永远没有足够的内存来满足您的所有查询) '正在尝试编译,或者盒子上有其他进程正在从 SQL Server 窃取内存)。
您可以使用以下命令查看 SQL Server 的配置:
您可以使用以下 Jonathan Kehayias 查询来识别需要最多编译内存的缓存计划,稍作修改:
您可以看到计划缓存如何与以下内容一起使用:
当您遇到高信号量等待时,请检查这些查询结果是否与“正常”活动期间有显着差异:
你可能还想看看内存是如何分布的:
这里有一些很好的信息,说明为什么您可能会看到大量的编译/重新编译(这将导致等待):
您可以使用以下计数器检查高编译/重新编译计数:
您可以检查导致驱逐的内部内存压力 - 这里的非零计数器表示计划缓存发生了不好的事情:
注意这些指标中的大多数都没有神奇的“天哪,我需要恐慌或做点什么!” 临界点。您需要做的是在正常系统活动期间进行测量,并确定这些阈值对于您的硬件、配置和工作负载的位置。当你
恐慌时,当两个条件为真时,你会做某事:Optimize for ad hoc workloads
对于 99% 的工作负载来说是一个很好的设置,但它对降低编译成本没有多大帮助 - 它旨在通过防止一次性计划在执行两次之前存储整个计划来减少计划缓存膨胀. 即使您只将存根存储在计划缓存中,您仍然必须编译完整计划以执行查询。也许@Kahn 的意思是建议将数据库级别的参数化设置为强制,这可能会提供更好的计划重用(但这实际上取决于所有这些高成本查询的独特性)。本白皮书中还有一些关于计划缓存和编译的好信息。
到目前为止,我看到出现这些等待的最典型原因是索引碎片化或不足,以及样本量不足或过时的统计数据。这会导致大量的全表扫描占用所有内存,进而产生我们经常看到的 RESOURCE_SEMAPHORE_QUERY_COMPILE 症状。
验证这一点的最简单方法是检查查询是否运行全表扫描/索引扫描,何时应该进行索引搜索。如果您有一个可以重现问题的问题查询 - 诊断和修复它变得非常容易。
我会检查受这些问题查询影响的表上的索引 - 即。检查索引碎片、未使用的潜在过滤索引、您可能想要创建的缺失索引等。此外,尽快使用 FULLSCAN 更新它们的统计信息。
要记住的一点是,您的问题表可能不是唯一需要这个的。例如,如果您有一个从 10 个表中获取数据的查询,执行计划程序可能偶尔会显示它没有使用表 1 上的索引,但是当您检查表 1 上的索引时,它实际上是可以的。查询计划器可能会正确地通过全表扫描来获取表 1 上的数据,因为例如表 7 上的错误/不足索引返回了太多数据,这将是更快的选择。所以诊断这些有时会很棘手。
此外,例如,如果您有大量代码隐藏查询,而变量值只发生了一些变化,您可能需要考虑启用针对临时工作负载的优化。基本上它所做的是存储已编译计划的存根而不是整个计划,当您每次都无法获得完全相同的计划时可以节省资源。