主页 / dba / 问题 / 117306
Accepted
Geoff Patterson
Asked: 2015-10-08 09:17:13 +0800 CST

在 SQL Server 2014 中查询慢 100 倍，Row Count Spool 行估计是罪魁祸首？

  • 772

我有一个查询在 SQL Server 2012 中运行 800 毫秒，在 SQL Server 2014 中需要大约170 秒。我认为我已将其缩小到对Row Count Spool运营商的基数估计不佳。我已经阅读了一些关于 spool 操作符的信息（例如，这里和这里），但仍然无法理解一些事情：

  • 为什么这个查询需要一个Row Count Spool操作符？我认为正确性没有必要，那么它试图提供什么具体的优化呢？
  • 为什么 SQL Server 估计连接到Row Count Spool运算符会删除所有行？
  • 这是 SQL Server 2014 中的错误吗？如果是这样，我将在 Connect 中归档。但我想先有更深入的了解。

注意：我可以将查询重写为 aLEFT JOIN或向表中添加索引，以便在 SQL Server 2012 和 SQL Server 2014 中实现可接受的性能。所以这个问题更多的是关于深入了解这个特定的查询和计划，而不是关于如何以不同的方式表达查询。


慢查询

有关完整的测试脚本，请参阅此 Pastebin。这是我正在查看的特定测试查询：

-- Prune any existing customers from the set of potential new customers
-- This query is much slower than expected in SQL Server 2014 
SELECT *
FROM #potentialNewCustomers -- 10K rows
WHERE cust_nbr NOT IN (
    SELECT cust_nbr
    FROM #existingCustomers -- 1MM rows
)


SQL Server 2014：估计的查询计划

SQL Server 认为这Left Anti Semi Join会将Row Count Spool10,000 行过滤到 1 行。出于这个原因，它选择 aLOOP JOIN用于后续连接到#existingCustomers。

在此处输入图像描述


SQL Server 2014：实际的查询计划

正如预期的那样（除了 SQL Server 之外的所有人！），Row Count Spool没有删除任何行。因此，当 SQL Server 预计只循环一次时，我们循环了 10,000 次。

在此处输入图像描述


SQL Server 2012：估计的查询计划

使用 SQL Server 2012（或OPTION (QUERYTRACEON 9481)在 SQL Server 2014 中）时，Row Count Spool不会减少估计的行数并选择哈希连接，从而产生更好的计划。

在此处输入图像描述

LEFT JOIN 重写

作为参考，这是一种我可以重写查询的方法，以便在所有 SQL Server 2012、2014 和 2016 中实现良好的性能。但是，我仍然对上述查询的具体行为以及是否它感兴趣是新的 SQL Server 2014 基数估计器中的一个错误。

-- Re-writing with LEFT JOIN yields much better performance in 2012/2014/2016
SELECT n.*
FROM #potentialNewCustomers n
LEFT JOIN (SELECT 1 AS test, cust_nbr FROM #existingCustomers) c
    ON c.cust_nbr = n.cust_nbr
WHERE c.test IS NULL

在此处输入图像描述

sql-server performance

3 个回答

  1. Best Answer

    为什么此查询需要 Row Count Spool 运算符？...它试图提供什么具体的优化？

    中的cust_nbr列#existingCustomers可以为空。如果它实际上包含任何空值，则此处的正确响应是返回零行（NOT IN (NULL,...) 将始终产生一个空结果集。）。

    所以查询可以被认为是

    SELECT p.*
FROM   #potentialNewCustomers p
WHERE  NOT EXISTS (SELECT *
                   FROM   #existingCustomers e1
                   WHERE  p.cust_nbr = e1.cust_nbr)
       AND NOT EXISTS (SELECT *
                       FROM   #existingCustomers e2
                       WHERE  e2.cust_nbr IS NULL)

    使用 rowcount spool 以避免必须评估

    EXISTS (SELECT *
        FROM   #existingCustomers e2
        WHERE  e2.cust_nbr IS NULL)

    不止一次。

    这似乎只是假设的微小差异可能在性能上造成灾难性差异的情况。

    如下更新单行后...

    UPDATE #existingCustomers
SET    cust_nbr = NULL
WHERE  cust_nbr = 1;

    ...查询在不到一秒的时间内完成。该计划的实际版本和估计版本中的行数现在几乎是准确的。

    SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT *
FROM   #potentialNewCustomers
WHERE  cust_nbr NOT IN (SELECT cust_nbr
                        FROM   #existingCustomers 
                       )

    在此处输入图像描述

    如上所述输出零行。

    SQL Server 中的统计直方图和自动更新阈值不够精细，无法检测这种单行更改。可以说，如果该列可以为空，则基于它包含至少一个列可能是合理的，NULL即使统计直方图当前未指示存在任何列。

    • 11

  2. 为什么此查询需要 Row Count Spool 运算符？我认为正确性没有必要，那么它试图提供什么具体的优化呢？

    请参阅Martin 对这个问题的详尽回答。NOT IN关键点是，如果is中的单行NULL，则布尔逻辑的结果是“正确的响应是返回零行”。运营商正在优化这个Row Count Spool（必要的）逻辑。

    为什么 SQL Server 估计与 Row Count Spool 运算符的联接会删除所有行？

    Microsoft 提供了关于 SQL 2014 Cardinality Estimator 的出色白皮书。在本文档中，我找到了以下信息：

    新的 CE 假定查询的值确实存在于数据集中，即使该值超出了直方图的范围。此示例中的新 CE 使用通过将表基数乘以密度计算得出的平均频率。

    通常，这样的改变是非常好的。它极大地缓解了升序键问题，并且通常为基于统计直方图的超出范围的值生成更保守的查询计划（更高的行估计）。

    但是，在这种特定情况下，假设NULL将找到一个值会导致假设加入Row Count Spool将过滤掉 中的所有行#potentialNewCustomers。在实际上有NULL一行的情况下，这是一个正确的估计（如马丁的回答所示）。但是，在碰巧没有NULL行的情况下，效果可能是毁灭性的，因为无论出现多少输入行，SQL Server 都会生成 1 行的连接后估计值。这可能导致查询计划的其余部分中的连接选择非常差。

    这是 SQL 2014 中的错误吗？如果是这样，我将在 Connect 中归档。但我想先有更深入的了解。

    我认为它处于错误和 SQL Server 的新基数估计器的性能影响假设或限制之间的灰色区域。NOT IN但是，在碰巧没有任何NULL值的可为空子句的特定情况下，这种怪癖可能会导致性能相对于 SQL 2012 大幅下降。

    因此，我提交了一个 Connect 问题，以便 SQL 团队了解此更改对 Cardinality Estimator 的潜在影响。

    更新：我们现在在 CTP3 上使用 SQL16，我确认那里没有出现问题。

    • 9

  3. 马丁史密斯的回答和你的自我回答正确地解决了所有要点，我只想为未来的读者强调一个领域：

    所以这个问题更多的是关于深入理解这个特定的查询和计划，而不是关于如何以不同的方式表达查询。

    查询的既定目的是：

    -- Prune any existing customers from the set of potential new customers

    这个需求很容易在 SQL 中以多种方式表达。选择哪一个与其他任何事情一样多是风格问题，但仍应编写查询规范以在所有情况下返回正确的结果。这包括考虑空值。

    充分表达逻辑要求：

    • 返回尚未成为客户的潜在客户
    • 最多列出每个潜在客户一次
    • 排除空潜在客户和现有客户（无论空客户是什么意思）

    然后，我们可以使用我们喜欢的任何语法编写符合这些要求的查询。例如：

    WITH DistinctPotentialNonNullCustomers AS
(
    SELECT DISTINCT 
        PNC.cust_nbr 
    FROM #potentialNewCustomers AS PNC
    WHERE 
        PNC.cust_nbr IS NOT NULL
)
SELECT
    DPNNC.cust_nbr
FROM DistinctPotentialNonNullCustomers AS DPNNC
WHERE
    DPNNC.cust_nbr NOT IN
    (
        SELECT 
            EC.cust_nbr 
        FROM #existingCustomers AS EC 
        WHERE 
            EC.cust_nbr IS NOT NULL
    );

    这会产生一个高效的执行计划，它会返回正确的结果：

    执行计划

    我们可以表达NOT IN为<> ALL或NOT = ANY不影响计划或结果：

    WITH DistinctPotentialNonNullCustomers AS
(
    SELECT DISTINCT 
        PNC.cust_nbr 
    FROM #potentialNewCustomers AS PNC
    WHERE 
        PNC.cust_nbr IS NOT NULL
)
SELECT
    DPNNC.cust_nbr
FROM DistinctPotentialNonNullCustomers AS DPNNC
WHERE
    DPNNC.cust_nbr <> ALL
    (
        SELECT 
            EC.cust_nbr 
        FROM #existingCustomers AS EC 
        WHERE 
            EC.cust_nbr IS NOT NULL
    );

    WITH DistinctPotentialNonNullCustomers AS
(
    SELECT DISTINCT 
        PNC.cust_nbr 
    FROM #potentialNewCustomers AS PNC
    WHERE 
        PNC.cust_nbr IS NOT NULL
)
SELECT
    DPNNC.cust_nbr
FROM DistinctPotentialNonNullCustomers AS DPNNC
WHERE
    NOT DPNNC.cust_nbr = ANY
    (
        SELECT 
            EC.cust_nbr 
        FROM #existingCustomers AS EC 
        WHERE 
            EC.cust_nbr IS NOT NULL
    );

    或使用NOT EXISTS：

    WITH DistinctPotentialNonNullCustomers AS
(
    SELECT DISTINCT 
        PNC.cust_nbr 
    FROM #potentialNewCustomers AS PNC
    WHERE 
        PNC.cust_nbr IS NOT NULL
)
SELECT
    DPNNC.cust_nbr
FROM DistinctPotentialNonNullCustomers AS DPNNC
WHERE 
    NOT EXISTS
    (
        SELECT * 
        FROM #existingCustomers AS EC
        WHERE
            EC.cust_nbr = DPNNC.cust_nbr
            AND EC.cust_nbr IS NOT NULL
    );

    这并没有什么神奇之处，也没有什么特别令人反感的使用IN, ANY, 或ALL- 我们只需要正确编写查询，因此它总是会产生正确的结果。

    最紧凑的形式使用EXCEPT：

    SELECT 
    PNC.cust_nbr 
FROM #potentialNewCustomers AS PNC
WHERE 
    PNC.cust_nbr IS NOT NULL
EXCEPT
SELECT
    EC.cust_nbr 
FROM #existingCustomers AS EC
WHERE 
    EC.cust_nbr IS NOT NULL;

    这也会产生正确的结果，尽管由于缺少位图过滤，执行计划可能效率较低：

    非位图执行计划

    最初的问题很有趣，因为它通过必要的空检查实现暴露了一个影响性能的问题。这个答案的重点是正确编写查询也可以避免这个问题。

    • 5

相关问题