更改數據庫的兼容級別會導致儲存過程的性能提高
我們在 Windows Server 2012 Datacenter (IIS 8) 中有一個 Web 應用程序，在 .NET Framework 4.0 下執行，它使用 SQL Server 2012 連接到另一個 Windows Server 2012 Datacenter。我的數據庫有一個儲存過程，它獲取 3 個參數 UserID、StartDate 和結束日期。這個儲存過程執行得很好，但在月底（當所有 +40.000 客戶都使用它時）開始逐漸降低其性能。
奇怪的是，當我們將兼容性級別更改為 Sql Server 2008 時，它會自動“刷新”並開始非常快地工作，然後再次逐漸降低其性能，直到我們（再次）將兼容性槓桿帶回 2005 並開始工作適當地。
為什麼主記憶體儲過程改變Compatibility Level後性能逐漸降低了很多呢？
這是因為當您在 SQL Server 中修改任何數據庫的兼容性級別時，更改會受到影響。我相信這是從 SQL Server 2008 開始出現的影響，至少從那時起它出現在文件中。
正如 MSDN 上針對ALTER DATABASE SET COMPATIBILITY_LEVEL所述：
兼容性級別和儲存過程
當儲存過程執行時，它使用定義它的數據庫的目前兼容級別。當數據庫的兼容性設置發生變化時，它的所有儲存過程都會相應地自動重新編譯。
因此，您基本上會看到目前記憶體中的每個查詢計劃都被標記為重新編譯，這將包括如上所述的儲存過程。因此，只有重新編譯了該儲存過程的計劃，才能看到您最初的性能改進。然後在執行了一段時間後，該計劃還不夠。
而不是在兼容性級別之間來回切換，您應該嘗試優化過程，以便生成的查詢計劃對於傳遞的參數值範圍更優化。快速的替代方法是，如果無法立即完成程式碼更改，則可能只是在大量使用期間安排一個作業，以最初標記要重新編譯的過程。
除了Shawn Melton，因為我還不能評論，你可以定期更新你的統計數據。這將生成一個新的執行計劃。之後重新編譯你的儲存過程
sp_recompile。這是一個很棒的腳本的連結：https ://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html