Microsoft SQL Server 2016 儲存過程的性能下降稱為 1000 次

語境 ：

小表，儲存過程更新表，前兩千次點擊沒問題。在第一次命中之後，它開始以蝸牛的速度爬行。此儲存過程在我們目前的 SQL Server 2008 R2 環境中與呼叫 C# 應用程序沒有問題。在我們的 SQL Server 2016 新測試環境中，我們看到了這個問題。查詢的違規部分處理 where 子句，該子句對不等於更新小表的參數的列進行了四次比較：

WHERE A.Column1 <> @param1 OR A.Column2 <> @param2     etc..

我的假設是參數嗅探，但已經通過各種查詢提示排除了這一點。

我也試過：

• 更改為選擇不更新
• 使用並行度 (MAXDOP)
• 更新統計資訊並重新編譯，甚至在過程中進行
• 索引的各種變化
• 在 X 次迭代後在呼叫 C# 程式碼時重置連接池
• 刪除任何交易控制

似乎可以解決問題的唯一更改是刪除 where 子句並將邏輯推送到 setter：

SET A.Column1 = CASE 
       WHEN A.Column1 <> @param1
           THEN @param1
       ELSE A.Column1
       END

我不喜歡更改程式碼，但我沒有找到其他線上資源可以回答 SQL Server 2016 會中斷的原因。

任何幫助，將不勝感激。

在 SQL Server 2014 及更高版本中，引入了新的基數估計邏輯。

來自 BOL：

基數估計邏輯（稱為基數估計器）在 SQL Server 2014 中進行了重新設計，以提高查詢計劃的質量，從而提高查詢性能。新的基數估計器結合了適用於現代 OLTP 和數據倉庫工作負載的假設和算法。它基於對現代工作負載的深入基數估計研究，以及我們過去 15 年改進 SQL Server 基數估計器的經驗。來自客戶的回饋表明，雖然大多數查詢將受益於更改或保持不變，但與之前的基數估計器相比，少數查詢可能會出現回歸。

最近，我們從 SQL Server 2012 升級到 SQL Server 2014，並受到新的基數估計器不足的打擊——查詢超時，cpu 接近 100%。

經過多次故障排除、更新統計資訊、重建索引、進行查詢計劃分析，我們發現將兼容性級別更改為 sql 2012 效果很好。

Paul White 解釋 -多個謂詞的基數估計

如果數據庫兼容級別設置為低於 120，或者如果跟踪標誌 9481 處於活動狀態，則 SQL Server 2014 中的選擇性計算行為與以前的版本相同（並且跟踪標誌 4137 與以前一樣工作）。

所以我的建議是

• 對於揭示問題的少量查詢，請使用 QUERYTRACEON(9481) 提示。
• 如果您不想賭博，則只需將跟踪標誌TF9481作為啟動參數，以便在伺服器重新啟動期間保持不變。

注意：啟用TF 9481，您不需要將數據庫的兼容級別設置為較低的級別。

來自KB2801413：

9481：在預設數據庫兼容級別 120 執行 SQL Server 2014 時使用。跟踪標誌 9481 強制查詢優化器在創建查詢計劃時使用基數估計器的版本 70（SQL Server 2012 版本）。

作為旁注，除了適當的測試 - 您還想研究TF4199（將其視為打開查詢優化器的每個修復的萬能鑰匙）。TF4199 行為隨 sql server 2016的變化而變化。。TF4199 在我的環境中提供了很多幫助，並且預設情況下對於所有新安裝都是開啟的。

在 SQL Server 2016 中，您不需要打開跟踪標誌9481。

SELECT  name, value  
   FROM  sys.database_scoped_configurations  
   WHERE name = 'LEGACY_CARDINALITY_ESTIMATION'; 
-- if above is having value = 0, then set to ON
ALTER DATABASE
   SCOPED CONFIGURATION  
       SET LEGACY_CARDINALITY_ESTIMATION = ON;  
go  

對於 Trace Flag 4199，你可以這樣做

ALTER DATABASE
   SCOPED CONFIGURATION  
       SET QUERY_OPTIMIZER_HOTFIXES = ON

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