更改我的數據庫的兼容性級別時查詢執行緩慢
我使用 SQL Server 2014 並且有一個兼容級別為 110 (SQL Server 2012) 的數據庫。我還有一個執行大約 10 秒的儲存過程。當我將數據庫的兼容級別更改為 120 (SQL Server 2014) 時,我的儲存過程的執行時間變成了大約 30 分鐘。
我使用以下步驟,但我的儲存過程的執行時間沒有改變。
第一步:使用
DBCC CHECKDB
命令。第 2 步:
DBCC UPDATEUSAGE
在我的數據庫上執行。第 3 步:重建我的數據庫上的所有索引。
第 4 步:刪除我的數據庫中的所有索引和所有外鍵,然後重新創建。
完成上述所有步驟後,我的儲存過程執行時間沒有改變。
但是當我的數據庫的兼容性恢復到 110 時,我的 sp 執行了大約 10 秒。
我檢查了上面的多個步驟並得到一個結果。
我該怎麼辦。以及如何將我的數據庫的兼容性級別更改為 120 (SQL Server 2014)。
提前致謝。
我最近看到了從 SQL Server 2008 R2 升級到 SQL Server 2014 的完全相同的問題,其中極少數儲存過程 (SP)(大約 3,000 個中的 5 個)在 SQL 2014 兼容級別 120 中的性能比在 SQL 2014 中更差兼容級別 110 或 SQL 2008。
在 SQL 2014 中,基數估計器 (CE) 發生了變化,這可能會影響 SP 在 SQL 2014 兼容級別 120 中的執行方式。
有幾種可能的解決方案
- 讓 SQL 2014 數據庫以兼容級別 120 執行並添加 dbcc 跟踪標誌以強制 SQL Server 2014 使用之前的 CE <–我不推薦這樣做
- 讓 SQL 2014 數據庫以兼容級別 120 執行並臨時添加
到有問題的 SP 以強制該 SP 使用先前的 CE。SP 現在應該在兼容級別 120 中執行,就像在兼容級別 110 中一樣。 <– 我更喜歡這種方法,因為您的數據庫/系統獲得了 SQL 2014 的所有其他優勢,而“修復”只影響有問題的 SP。
無論採用哪種方法,我都會在 SQL Server 2014 兼容級別 120 的測試系統上分析有問題的 SP 的執行計劃/讀/寫等,並重寫 SP 以適應新的 CE。
一旦經過測試和驗證,我就會申請生產。
Microsoft 有一篇關於SQL 2014 中基數估計器更改的好文章
您可以嘗試更新統計資訊(重建索引不會重新計算所有統計資訊):
EXEC sp_updatestats
並重新編譯它們:
EXEC sp_recompile N'schema.yourProcedure';
找到解決方案並使用兼容級別 120 可能比使用舊優化器更好。