SQL查询优化器-成本随资源可用性而变化

Question

当查询优化器计算执行计划的成本时，可以根据其可用的资源数量来期望计划不同吗？

以下列情况为例：

• 您有一个运行Server 2008 R2的R2虚拟机，内存为32 up，8个CPU核心连接到SAN上。有7个VM共享主机框。
• 您会发现CPU使用率经常达到130%的峰值，因此您将大部分VM移出主机，只留下原始7个VM中的3个，包括SQL Server框。
• 您会立即发现，由于有更多的CPU空间可用，实际性能要慢一些。您可以看到，来宾上的CPU利用率比以前的30%要高得多(因为有更多的CPU资源可供使用)。尽管如此，应用程序的用户看到性能明显下降。

我对SQL VM在可用更多CPU资源时执行速度较慢的解释是，过程缓存包含在/优化了CPU可用性较低的环境下计算的计划。当额外的资源可用时，过程缓存将提供不太理想的计划，这些计划可能执行得不太好。

这有道理吗？几周前，我们就有了这样的场景，当我们停止CPU在主机上的颠簸时，我们就对性能提出了很多抱怨。我运行dbcc freeprocache，此后性能似乎有所提高--或者是用户习惯了它。

有什么想法？谢谢

Answer 1

首先，一些背景信息

到目前为止(直到Server 2014)，查询优化器将只考虑以下几点：

• 系统上可用的CPU核数。
• 当前使用的内存量。

CPU核心:从关联掩码分配给Server的核心计数决定了创建并行计划的效率。例如，在一个有4个核的系统上，当切换到一个并行计划时，你可以期望一个4倍的加速，而一个有2个核的系统只能得到一倍。正因为如此，核心计数会影响计划的选择。不幸的是，并不是每个查询都能与核心计数线性地扩展，但是优化者会相信它是这样的。这意味着，在12~16核以上的机器上，获得更多的并行性实际上会减缓查询的速度。CPU的速度没有考虑到，只考虑了核心的数量。

可用内存:计划制定时，将考虑可用内存的数量。这决定了哈希连接、排序空间和其他内存密集型操作等策略。这里的错误估计是非常危险的，可能导致不良的性能。特别是如果您高估了散列连接可用的内存，并且不得不泄漏到tempdb中。

您的具体案例

如果没有对系统的测量，那么很难，如果不是不可能的话，就很难准确地知道在您的场景中发生了什么。有太多的变量可能同时发生变化。可能只是环境中的其他东西发生了变化。任何诊断都是纯粹的猜测，而真正的DBA工作是一门科学，而不是猜测的艺术练习。

你需要收集以下信息才能获得知识。

• 查询之前/之后的计划
• 等待统计
• 精确的机器配置(在虚拟化场景中主机和VM都是如此)