查詢記憶體授予和 tempdb 溢出
我有一個長時間執行的查詢（具有 1 億行的事實表加入了一些小的暗表然後分組）溢出到 tempdb，即使（經過一些調整）CE 非常接近實際的行數，請參閱計劃：
尋找解釋，我注意到以下記憶體授予資訊：
環境：SQL Server 2012 SP1 Enterprise，伺服器 RAM 256 GB，SQL Server 最大記憶體 200 GB，緩衝池大小 42 GB，工作區最大大小 156 GB（GrantedMemory = 156 * 25% ~= 38 GB）
問題
- 這是否意味著無論CE有多好，查詢都沒有機會不溢出？因為查詢最大 ram 硬上限為 38 GB
- 查詢優化器在建構計劃時是否不考慮最大查詢記憶體？（強制雜湊匹配聚合將消除排序步驟並顯著提高查詢性能，不幸的是，實際查詢來自 Cognos，我們無法控制它）
- 將 25% 的上限提高到接近 100% 是一個明智的選擇嗎？（假設可以控制所述伺服器訪問限制並發查詢請求數）
粘貼計劃中的匿名查詢計劃
當強制雜湊匹配聚合（而不是排序 + 流聚合）時，查詢始終快 3 - 4 倍。不幸的是，實際查詢來自 Cognos，我們無法更改它。
雜湊聚合計劃中沒有雜湊溢出。查詢優化器不會選擇雜湊匹配聚合，因為如果我查看雜湊與流聚合的運算符成本，雜湊組的 CPU 成本是流聚合的 2-3 倍。
在流聚合和散列聚合中，估計的輸出行與輸入完全相同（約 1 億行）。
該查詢使用單個 NC 列儲存索引，並且列統計資訊都定期更新。
- 這是否意味著無論CE有多好，查詢都沒有機會不溢出？因為查詢最大 ram 硬上限為 38 GB
鑑於您目前的硬體和 SQL Server 配置，您的查詢的總記憶體授予上限為 37GB。
如果無法在查詢記憶體授予的記憶體分數（該計劃中的 0.860743）內執行排序，它將溢出到tempdb。另請注意，此並行排序將其部分查詢記憶體授權平均分配給 12 個執行緒，並且此分配不能在執行時重新平衡。
- 查詢優化器在建構計劃時是否不考慮最大查詢記憶體？（強制雜湊匹配聚合將消除排序步驟並顯著提高查詢性能，不幸的是，實際查詢來自 Cognos，我們無法控制它）
是的，確實如此，但僅作為一般成本計算框架的輸入。優化器根據其模型選擇看起來最便宜的計劃。如果數字錯誤，則計劃選擇不太可能是最優的。
在您的情況下，Stream Aggregate 生成的實際行數明顯少於估計的：
當需要更少、更大的組時（因為每個組佔用雜湊表中的一個槽），優化器傾向於使用 Hash Aggregate。有關密度的錯誤資訊會導致錯誤選擇 Sort + Stream Aggregate。
最好的計劃可能是散列連接而不是嵌套循環連接和散列聚合。這應該能夠將批處理模式處理擴展到重要的聚合步驟。
SQL Server 2012 在行模式和批處理模式之間的轉換非常有限。一旦行模式處理開始，執行引擎就永遠不會返回批處理模式（因此 row-batch-row 可以，但 batch-row-batch 不行）。
- 將 25% 的上限提高到接近 100% 是一個明智的選擇嗎？（假設可以控制所述伺服器訪問限制並發查詢請求數）
如果您想增加此查詢的可用記憶體量，您當然可以通過更改您的資源調控器設置來實現。逐步增加限制，看看您是否可以找到一個好的折衷方案。我會警惕過於接近 100%。
如果查詢適用於計劃指南，請嘗試
HASH GROUP提示。從長遠來看，升級到 SQL Server 2016 將帶來好處，因為更多的操作員可以在批處理模式（包括排序）下執行，動態記憶體授予增加是可能的，並且……一般來說，列儲存/批處理模式處理方面的其他大約一千個改進。
我可以部分回答你的問題。
1）我不確定我是否完全理解你的問題。由於基數估計錯誤，SQL 伺服器只會溢出到 tempdb 是不正確的。有時 SQL Server 期望足夠好的計劃會溢出到 tempdb。
- 查詢優化器在建構計劃時確實考慮了伺服器上的記憶體。一個有用的練習可能是更改查詢可用的記憶體量，以查看查詢計劃如何更改。您可以通過更改伺服器上的記憶體設置、使用資源管理器或未記錄的命令DBCC OPTIMIZER WHAT_IF()來做到這一點。如果您想查看記憶體超過 200 GB 的查詢計劃是什麼樣子，那麼 WHAT_IF 很有用。
正如您所指出的，查詢優化器不使用雜湊匹配聚合，因為它認為該運算符的 CPU 成本將遠高於排序。使散列匹配聚合對優化器有吸引力的標準之一是 SQL Server 估計不會返回許多不同的行。對於您的查詢，SQL Server 認為它不會使用 GROUP BY 消除任何行。
計劃的估計成本有多接近？當您更改可用於查詢的記憶體時，它們如何變化？
3）我不知道，但這絕對是你應該仔細測試的東西。更安全的選擇是增加 SQL Server 最大記憶體（200 似乎有點低，但伺服器上可能安裝了其他應用程序，或者這超出了您的控制範圍）或提高 tempdb 性能。我可以想出一些其他的提高性能的想法，但它們都是遙不可及的。
嘗試執行一個只對事實表執行 GROUP BY 的更簡單的查詢。有什麼方法可以更好地估計不同值的數量？創建多列統計資訊有幫助嗎？
如果您無法更改查詢，您可以嘗試替換視圖引用的表，該視圖選擇您需要的數據，但以更改計劃的方式。這在某些情況下會有所幫助，但我想不出在這裡應用該技術的方法。
聽起來您對該伺服器有相當多的控制權，因此您可以嘗試創建計劃指南。我從來沒有這樣做過，也從未聽過任何人對計劃指南說任何積極的話。