預登台數據導致執行計劃成本飆升

我有一個麻煩的查詢，我們正在嘗試調整。我們的第一個想法是獲取更大執行計劃的一部分並將這些結果儲存到中間臨時表中，然後執行其他操作。

我觀察到的是，當我們將數據預置到臨時表中時，執行計劃成本會飆升（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 是同一個對象。

成本基於估計，即使在“實際計劃”中也是如此。您不能並排比較兩個查詢計劃並得出結論，僅根據運算符或總計劃成本，其中一個將需要更多 CPU 來執行。我可以創建一個在一秒鐘內執行的花費數百萬的查詢。我還可以創建一個成本很小的查詢，而這實際上需要永遠執行。對於您的情況，第一個查詢的成本僅為 22 個優化器單元，因為散列連接後基數估計不佳：

紅色的運算元執行了數百萬次，但查詢優化器預計它們只執行幾千次。基於估計的成本不會反映這項工作。藍色的運算符是一個表假離線，基數估計器希望為其插入一行。相反，它插入了幾百萬。結果，黑色的運算符（以及其他一些未顯示的運算符）效率低下並溢出到 tempdb。

使用另一個計劃，您將大量行放入 tempdb 中，因此基數估計更合理，儘管它仍然不理想：

查詢優化器預計需要處理更多的行，因此查詢計劃的成本更高。作為一個非常普遍的經驗法則，您可能會通過改進的估計看到改進的性能，但它並不總是能達到您想要的效果。查看帶有臨時表的計劃，我看到​​了一些可能改進的領域：

1. 將原始查詢中的完整 CTE 載入到臨時表中。具有多個不同聚合的查詢可能很難優化。有時您會得到一個查詢計劃，其中所有數據都載入到一個假離線（到 tempdb）中，並且一些聚合分別應用於假離線。根據我的經驗，所有這些工作總是在一個串列區域中完成。如果您消除查詢中的所有聯接，我相信您將無法獲得該優化。聚合將僅應用於臨時表。這將節省您將幾乎相同的數據寫入 tempdb 的工作，並且整個計劃應該符合併行性的條件。
2. 將臨時表定義為堆並使用TABLOCK. 看起來現在您有一個聚集索引，這意味著您沒有資格進行並行插入。
3. 考慮使用這些技巧之一使查詢符合批處理模式。使用多個不同的聚合，批處理模式聚合可以顯著提高效率。

我希望這些步驟的某種組合能夠顯著改善執行時間。請注意，我進行了快速分析，部分原因是匿名計劃難以解釋。

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