主页 / dba / 问题 / 206180
Accepted
Kris Gruttemeyer
Asked: 2018-05-09 10:22:01 +0800 CST

预暂存数据导致执行计划成本飙升

  • 772

我有一个我们正在尝试调整的麻烦查询。我们最初的想法之一是获取较大执行计划的一部分并将这些结果存储到中间临时表中，然后执行其他操作。

我观察到的是，当我们将数据预存到临时表中时，执行计划成本会飙升 (22 -> 1.1k)。现在，这具有允许计划并行执行的好处，这将执行时间减少了 20%，但在我们的案例中，每次执行的 CPU 使用率更高，这并不值得。

我们正在使用带有旧版 CE 的 SQL Server 2016 SP2。

原计划（成本~20）：

低成本计划

https://www.brentozar.com/pastetheplan/?id=ry-QGnkCM

原始 SQL：

WITH Object1(Column1, Column2, Column3, Column4, Column5, Column6)
AS
(
    SELECT  Object2.Column1, 
            Object2.Column2, 
            Object3.Column3, 
            Object3.Column4, 
            Object3.Column5, 
            Object3.Column6
    FROM Object4 AS Object5
    INNER JOIN Object6 AS Object2 ON Object2.Column2 = Object5.Column2 AND Object2.Column7 = 0
    INNER JOIN Object7 AS Object8 ON Object8.Column8 = Object2.Column9 AND Object8.Column7 = 0
    INNER JOIN Object9 AS Object3 ON Object3.Column10 = Object8.Column11 AND Object3.Column7 = 0
    INNER JOIN Object10 AS Object11 ON Object2.Column1 = Object11.Column1
    WHERE   Object8.Column12 IS NULL AND
            Object8.Column13 = Object5.Column13 AND 
            Object3.Column3 = Object5.Column3 AND 
            Object11.Column14 = Variable1
)
insert Object12
SELECT  Object13.Column2,
        Object13.Column3,
        MIN(Object13.Column4) AS Column15,
        MAX(Object13.Column4) AS Column16,
        COUNT(DISTINCT (CASE WHEN Object13.Column5 = 1 THEN Object13.Column1 END)) AS Column17,
        COUNT(DISTINCT (CASE WHEN Object13.Column6 = 0 THEN Object13.Column1 END)) AS Column18,
        COUNT(DISTINCT Object13.Column1) AS Column19
FROM Object1 AS Object13
GROUP BY Object13.Column2, Object13.Column3 OPTION (RECOMPILE)

新计划（上面以蓝色突出显示的区域已预先安排到临时表中 - 成本约为 1.1k）：

在此处输入图像描述

https://www.brentozar.com/pastetheplan/?id=rycqG3JRf

新的 SQL：

SELECT  Object1.Column1,
        Object1.Column2,
        MIN(Object2.Column3) AS Column4,
        MAX(Object2.Column3) AS Column5,
        COUNT(DISTINCT (CASE WHEN Object2.Column6 = 1 THEN Object1.Column7 END)) AS Column8,
        COUNT(DISTINCT (CASE WHEN Object2.Column9 = 0 THEN Object1.Column7 END)) AS Column10,
        COUNT(DISTINCT Object1.Column7) AS Column11
from Object3 Object1
join Object4 Object2 on Object2.Column12 = Object1.Column13 and Object2.Column2 = Object1.Column2
where Object2.Column14 = 0
GROUP BY Object1.Column1, Object1.Column2 OPTION (RECOMPILE)

有人可以帮助我们理解为什么新计划会有如此大的成本吗？如果需要，我很乐意在下面提供有关表/索引的其他信息。

在原始计划的情况下，我们确实意识到它正在执行插入而不是选择。即便如此，选择（在我们看来）不应该那么昂贵。

这是实际的执行计划。这是一个问题，因为由于计划成本高得多，它是并行的。因此使用更高的CPU。此外，我们只是想知道为什么计划成本会因为预暂存数据之类的事情而上升那么多，这通常会让你接近甚至更好，而不是原始成本。

临时表在第二个查询中被索引为 Object1.Column13 和 Object1.Column2 上的复合集群 PK。这与 Object4 的列（和顺序）匹配。添加MAXDOP提示是一种选择，但这也是“世界上为什么成本会上涨那么多”的学术练习？

添加OPTION (ORDER GROUP)到第二个查询结果没有变化，相同的运营商/成本。

笔记：

  • 第一个查询中的 Object9 与第二个查询中的 Object4 是同一个对象。
sql-server performance

1 个回答

  1. Best Answer

    成本基于估计，即使是在“实际计划”中也是如此。您不能并排比较两个查询计划并得出结论，仅根据运算符或总计划成本，其中一个将需要更多 CPU 来执行。我可以创建一个在一秒钟内执行的数百万成本的查询。我还可以创建一个成本很小的查询，该查询实际上需要永远执行。对于您的情况，第一个查询的成本仅为 22 个优化器单元，因为散列连接后的基数估计不佳：

    在此处输入图像描述

    红色的运算符执行了数百万次，但查询优化器预计它们只会执行几千次。基于估算的成本不会反映该工作。蓝色的运算符是一个表假脱机，基数估计器希望为其插入一行。它反而插入了几百万。结果，黑色的运算符（以及其他几个未显示的运算符）效率低下并溢出到 tempdb。

    对于另一个计划，您将大量行放入 tempdb，因此基数估计更合理，但仍然不理想：

    在此处输入图像描述

    查询优化器预计需要处理更多的行，因此查询计划的成本更高。作为一个非常普遍的经验法则，您可能会看到改进的估计会提高性能，但它并不总是如您所愿。查看带有临时表的计划，我看到了几个可能改进的领域：

    1. 将原始查询中的完整 CTE 加载到临时表中。具有多个不同聚合的查询可能很难优化。有时您会得到一个查询计划，其中所有数据都加载到一个假脱机中（到 tempdb 中），并且一些聚合单独应用于假脱机。根据我的经验，所有这些工作总是在串行区域中完成。如果您消除查询中的所有连接，我相信您将无法获得优化。聚合将仅应用于临时表。这将节省您将几乎相同的数据写出到 tempdb 的工作，并且整个计划应该符合并行性。

    2. 将临时表定义为堆并使用TABLOCK. 看起来你现在有一个聚集索引，这意味着你不符合并行插入的条件。

    3. 考虑使用这些技巧之一使查询符合批处理模式。对于多个不同的聚合，批处理模式聚合可以显着提高效率。

    我希望这些步骤的某种组合能够显着改善运行时间。请注意，我做了一个快速分析，部分原因是匿名计划难以解释。

    • 6

相关问题