將數據庫兼容性從舊版 CE 更改為 120

只是從 DBA 的角度尋求專家/實用建議，遷移後我們在 SQL 2014 上執行的應用程序數據庫之一具有舊的數據庫兼容性級別，即 100。（SQL2008）

從 DEV 的角度來看，所有的測試都已經完成，他們沒有看到太大的差異，並希望根據他們的測試轉移到 prod。

在我們的測試中，對於我們在 SP 中看到緩慢的某些過程，我們發現語句的一部分很慢，並添加了查詢跟踪提示，如下所示，將 compat 保持為 120，這有助於保持性能穩定

SELECT  [AddressID],
   [AddressLine1],
   [AddressLine2]
FROM Person.[Address]
WHERE [StateProvinceID] = 9 AND
   [City] = 'Burbank'
OPTION (QUERYTRACEON 9481);
GO

更新-根據更多發現編輯問題-

實際上，我們發現在計算列中呼叫標量函式的表的情況變得更糟 -

下面是該列的外觀

CATCH_WAY AS ([dbo].[fn_functionf1]([Col1])) PERSISTED NOT NULL

並且奇怪的查詢的一部分看起來像下面

DELETE t2
  OUTPUT del.col1
         del.col2
         del.col3
  INTo #temp1
FROM #temp2 t2
INNER JOIN dbo.table1 tb1 on tb1.CATCH_WAY = ([dbo].[fn_functionf1](t2.[Col1])
AND t2.[col2] = tb1.[col2]
AND t3.[col3] = tb1.[col3]
AND ISNULL (t2.[col4],'') = ISNULL (tb1.[col4],'')

我知道正在呼叫函式並且速度很慢，但問題在於目前的兼容性，即 100 執行速度還不錯，但是當更改為 120 時，它會慢 X100 倍，如果保持在 100，它的 X100 速度會更快。怎麼了 ？

這種性能回歸通常是因為您已經有一個錯誤的查詢，在舊版本下被認為“足夠快”，但在新版本下卻超級慢，因為它的編寫方式很奇怪/糟糕。

我會在 DELETE 語句的 ON 子句中刪除 2 個函式，方法是在 DELETE 發生之前預先計算它們並將它們儲存到臨時表中。

您也可以嘗試重寫 ISNULL 部分，因為 ISNULL 也是一個函式。

--new temp table to store table1
SELECT col2
   ,col3
   ,col4
   ,CATCH_WAY --this scalar function gets calculated here and saved into the temp table
INTO #tempTable1
FROM dbo.table1;

DELETE t2
  OUTPUT del.col1
         del.col2
         del.col3
  INTO #temp1
FROM #temp2 t2
JOIN #tempTable1 tb1
ON tb1.CATCH_WAY = ([dbo].[fn_functionf1](t2.[Col1]) --not a good idea
AND t2.[col2] = tb1.[col2]
AND t3.[col3] = tb1.[col3]
AND ISNULL (t2.[col4],'') = ISNULL (tb1.[col4],'') --not a good idea, the ISNULL "hides" the columns from the SQL Optimiser

我盡力根據您的問題資訊解釋在這種情況下可以做什麼，希望對您有所幫助。

當遷移到新的 CE 時，您可以預期一些查詢執行計劃將保持不變，而另一些則會發生變化。這兩種情況都沒有本質上表明存在問題。

基於Microsoft Link和**Joseph Sack (SQLskills.com)**撰寫的 白皮書，我認為您必須注意第 18 頁白皮書中所寫的這些項目

要查看該文件，請下載使用 SQL Server 2014 Cardinality Estimator Word 優化您的查詢計劃文件。

如果您看到計劃回歸，您可以採取哪些措施？

如果您遇到新 CE 直接導致的性能下降，請考慮以下操作：

• 如果特定查詢仍然受益，則保留新的 CE 設置，並使用替代方法“圍繞”性能問題進行設計。例如，對於具有顯著事實表基數估計偏差的關係數據倉庫查詢，考慮遷移到列儲存索引解決方案。在這種情況下，您保留基數估計偏斜。但是，列儲存索引的補償性能改進可以消除舊版本的性能下降。

• 保留新CE，並對那些因新CE 直接導致性能下降的查詢使用跟踪標誌9481。這可能適用於工作負載中只有少數查詢因新 CE 導致性能下降的測試。

• 恢復到較舊的數據庫兼容級別，並使用跟踪標誌 2312 來處理使用新 CE 進行了性能改進的查詢。這可能適用於工作負載中只有少數查詢提高了性能的測試。

• 使用基本基數估計偏差故障排除方法。此選項不解決最初的根本原因。但是，使用基本的基數估計故障排除方法可能會解決總體估計偏差問題並提高查詢性能。

• 完全恢復到舊版CE。如果多個關鍵查詢遇到性能回歸併且您沒有時間圍繞這些問題進行測試和重新設計，這可能是合適的。

結論 我認為這些項目的基礎你必須改變一些參數，查詢，設置一些標誌等等，我不認為有像設置特殊參數這樣的快捷解決方案，突然一切都好！，我希望它對你有所幫助。

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