從 sql server 2008R2 升級到 2014sp2 後查詢變慢
我最近從 SQL Server 2008R2 升級到 2014SP2 並將數據庫升級到兼容級別 120。
此查詢使用舊的基數估計器（啟用了跟踪標誌 9481）生成更好的執行計劃：https://www.brentozar.com/pastetheplan/? id=Hy9skV1Ox
它僅在 object2 上使用 269 次邏輯讀取，在 object3 上使用 3 次。
- 使用新的基數估計器，我得到了這個執行計劃：https
//www.brentozar.com/pastetheplan/?id=BJAnpXk_e 不幸的是，這導致 object2 上的 269 次邏輯讀取和 object3 上的 68688 次邏輯讀取。
該伺服器針對臨時工作負載進行了配置。
即使啟用跟踪標誌 4199 也不能改進估計/執行計劃
為什麼新的基數估計器會產生如此糟糕的計劃？
為什麼估計差這麼遠？
除了禁用新的估算器或使用查詢提示之外，我還能做些什麼來改進它？
我理解您對您遇到的查詢計劃回歸感到失望。然而，微軟改變了一些關於基數估計器的核心假設。他們無法避免一些查詢計劃回歸。引用於爾根·托馬斯的話：
然而，為了清楚地說明這一點，與現有 CE 相比，避免任何回歸併不是目標。新的 SQL Server 2014 CE 沒有按照 QFE 的原則進行集成。這意味著我們的預期是，新的 SQL Server 2014 CE 將為許多查詢創建更好的計劃，尤其是複雜的查詢，但也會導致某些查詢的計劃比舊 CE 產生的更差。
為了回答您的第一個問題，優化器似乎選擇了新 CE 的更差計劃，因為 1 行基數估計來自
Object2. 這使得嵌套循環連接對優化器非常有吸引力。但是，實際返回的行數Object2是 34182。這意味著嵌套循環計劃的估計成本被低估了大約 30000 倍。舊版 CE 給出了 208.733 基數估計值
Object2。這仍然很遙遠，但足以使使用合併連接的計劃比嵌套循環連接計劃的估計成本更低。Object3SQL Server以 0.0032831 的成本進行非聚集索引查找。使用舊版 CE 下的嵌套循環計劃，我們可以預期 208 索引尋求的總成本約為 0.0032831 * 208.733 = 0.68529，這遠高於合併連接計劃的最終估計子樹成本 0.0171922。為了回答您的第二個問題，像您這樣簡單的查詢的基數估計公式實際上是由 Microsoft 發布的。我建議您參考此處找到的關於舊版和新版 CE 之間差異的優秀白皮書。關注為什麼新 CE 的基數估計為 1 而舊 CE 的基數估計為 208.733。這是出乎意料的，因為舊版 CE 假定濾波器獨立，但新版 CE 使用指數退避。一般來說，對於這樣的查詢，我希望新的 CE 給出更大的基數估計
Object2。您應該能夠通過查看Object2.要回答您的第三個問題，我們可以從白皮書中獲得一般策略。以下是一個簡短的報價：
- 如果特定查詢仍然受益，則保留新的 CE 設置，並使用替代方法“圍繞”性能問題進行設計。
- 保留新的 CE，並對那些因新 CE 直接導致性能下降的查詢使用跟踪標誌 9481。
- 恢復到較舊的數據庫兼容性級別，並使用跟踪標誌 2312 來處理使用新 CE 進行了性能改進的查詢。
- 使用基本基數估計偏差故障排除方法。
- 完全恢復到舊版 CE。
特別是對於您的問題，首先我將關注統計數據。我不清楚為什麼索引掃描的基數估計
Object3會如此遙遠。我建議FULLSCAN在進行更多測試之前更新所有涉及的對象和索引的統計資訊。更改 CE 後再次更新統計資訊也是一個很好的步驟。您應該能夠使用白皮書來弄清楚為什麼您會看到您所看到的基數估計。如果您提供更多資訊，我可以為您提供更詳細的幫助。我了解想要保護您的 IP，但您所擁有的只是一個非常簡單的查詢。您能否更改表名和列名並提供準確的查詢文本、相關的表 DDL、索引 DDL 以及有關統計資訊的資訊？
如果所有其他方法都失敗並且您需要在沒有提示或跟踪標誌的情況下修復它，您可以嘗試更新到 SQL Server 2016 或更改表上的索引。如果刪除索引，您不太可能得到錯誤的嵌套循環計劃，但當然刪除索引可能會對其他查詢產生負面影響。
出於某種原因，新的 CE 使用了不同的索引，這兩個索引都給出了 1 行的估計值，因此不建議使用循環連接。
您是否可以使用 FULLSCAN 更新兩個表的統計資訊？如果這沒有幫助，請發布表和索引定義（僅匿名表/列/索引名稱）。
旁白：
您的範常式式碼中的 ObjectX 別名非常令人困惑，您可能需要在下次需要匿名時重新考慮，尤其是對於更複雜的查詢。AliasX 用於所有別名，其中 X 與 ObjectX 匹配，可能是一種改進。
順便說一句，如果您沒有仔細考慮過 nolock 的一致使用，您可能需要閱讀以下內容：https ://sqlperformance.com/2015/04/t-sql-queries/the-read-uncommitted-isolation-等級