Sql-Server

更改我的數據庫的兼容性級別時查詢執行緩慢

  • December 27, 2015

我使用 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 中的執行方式。

有幾種可能的解決方案

  1. 讓 SQL 2014 數據庫以兼容級別 120 執行並添加 dbcc 跟踪標誌以強制 SQL Server 2014 使用之前的 CE <–我不推薦這樣做
  2. 讓 SQL 2014 數據庫以兼容級別 120 執行並臨時添加

選項(查詢跟踪 9481)

到有問題的 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 可能比使用舊優化器更好。

引用自:https://dba.stackexchange.com/questions/83344