在 SQL Server 2014 中查詢慢 100 倍，Row Count Spool 行估計是罪魁禍首？

我有一個查詢在 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

為什麼此查詢需要 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可以說，如果該列是可為空的，那麼即使統計直方圖目前沒有表明存在任何列，基於它包含至少一個列可能是合理的。

引用自：https://dba.stackexchange.com/questions/117306