OPTION FORCE ORDER 提高性能直到刪除行

我有一個有點複雜的 SQL Server 2008 查詢（大約 200 行相當密集的 SQL），它沒有按我的需要執行。隨著時間的推移，性能從大約 0.5 秒下降到大約 2 秒。

查看執行計劃，很明顯，通過重新排序連接，可以提高性能。我做到了，而且確實做到了……下降到大約 0.3 秒。現在查詢有“OPTION FORCE ORDER”提示，生活很好。

今天我來了，清理數據庫。我存檔了大約 20% 的行，除了刪除行之外，在相關數據庫中沒有採取任何行動……執行計劃完全被沖洗掉了。它完全錯誤地判斷了某些子樹將返回多少行，並且（例如）替換了：

<Hash>

和

<NestedLoops Optimized='false' WithUnorderedPrefetch='true'>

現在查詢時間從大約 0.3 秒增加到大約 18 秒。（！）只是因為我刪除了行。如果我刪除查詢提示，我會回到大約 2 秒的查詢時間。更好，但更糟。

在將數據庫還原到多個位置和伺服器後，我已經重現了該問題。簡單地從每個表中刪除大約 20% 的行總是會導致這個問題。

1. 這對於強制連接順序使查詢估計完全不准確（因此查詢時間不可預測）是否正常？
2. 我是否應該期望我將不得不接受次優的查詢性能，或者像鷹一樣觀察它並經常手動編輯查詢提示？或者也許也暗示每個加入？.3s 到 2s 是一個很大的打擊。
3. 刪除行後優化器爆炸的原因是否顯而易見？例如，“是的，它進行了樣本掃描，並且因為我在數據歷史記錄中較早歸檔了大部分行，所以樣本產生了稀疏的結果，所以它低估了對排序雜湊操作的需求”？

如果您想查看執行計劃，請建議我可以發布它們的位置。否則，我已經採樣了最令人驚嘆的部分。這是基本的錯誤估計，括號中的數字是（估計：實際）行。

                            /  Clustered Index Scan (908:7229)
Nested Loops (Inner Join) --<
                            \  NonClustered Index Seek (1:7229)

請注意，內部循環預計會掃描 908 行，而是掃描 52,258,441 行。如果它是準確的，這個分支將執行大約 2 毫秒，而不是 12 秒。在刪除行之前，這個內部連接估計值總共只有 2 倍，並且在兩個聚集索引上作為雜湊匹配執行。

這對於強制連接順序使查詢估計完全不准確（因此查詢時間不可預測）是否正常？

使用 FORCE ORDER 不會使估計不准確，刪除行確實如此。強制更新表上的統計資訊可以提高估計精度。

我是否應該期望我將不得不接受次優的查詢性能，或者像鷹一樣觀察它並經常手動編輯查詢提示？或者也許也暗示每個加入？.3s 到 2s 是一個很大的打擊。

最好確保優化器獲得生成最佳計劃所需的資訊，而不使用 FORCE ORDER 提示。通過這樣做，它應該可以更好地應對底層數據分佈的變化，而無需人工干預。也就是說，如果數據的性質使得基數可能每小時或每天都發生顯著變化，請考慮使用計劃指南來確保計劃是固定的。

刪除行後優化器爆炸的原因是否顯而易見？例如，“是的，它進行了樣本掃描，並且由於我在數據歷史記錄中較早歸檔了大部分行，因此樣本產生了稀疏的結果，因此它低估了對排序散列操作的需求”？

您沒有提到問題表中的行數，但很可能刪除：

• 沒有刪除足夠的行來觸發統計資訊更新。這應該在 20% 的行已被修改但可以選擇使用跟踪標誌 2371來啟用動態門檻值時發生。
• 確實觸發了統計更新，但收集的樣本不具有代表性。通過執行WITH FULLSCAN手動更新更正此問題。

您也可能會遇到老式的參數嗅探問題，有無數的選擇可以解決。WITH RECOMPILE可能是一個昂貴的選項來指定這麼大的查詢，但在過程和語句級別都值得研究。

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