從 SQL Server 2012 或更早版本升級至 2014 或更新版本的查詢效能降低
將 SQL Server 從 2012 或舊版升級至 2014 或更新版本之後，可能會遇到下列問題：大部分的原始查詢執行良好，但其中一些查詢的執行速度比舊版慢。 雖然有許多可能的原因和貢獻因素，但升級後基數估計（CE）模型的變化是比較常見的原因。 從 SQL Server 2014 開始，CE 模型已導入重大變更。
本文提供使用預設 CE 時所發生的查詢效能問題的疑難解答步驟和解決方案，但在使用舊版 CE 時不會發生。
注意
如果升級之後所有查詢的執行速度較慢，本文中導入的疑難解答步驟可能不適用於您的情況。
疑難解答：找出 CE 變更是否為問題，並找出原因
步驟 1：識別是否使用預設 CE
- 選擇升級后執行速度較慢的查詢。
- 執行查詢並 收集執行計劃。
- 從執行計劃 屬性視窗，檢查 CardinalityEstimationModelVersion。
- 值為 70 表示舊版 CE，而值為 120 或更高版本則表示使用預設 CE。
如果使用舊版CE，CE變更不是效能問題的原因。 如果使用預設 CE，請移至下一個步驟。
步驟 2：識別查詢優化器是否可以使用舊版 CE 產生更好的計劃
使用舊版 CE 執行查詢。 如果執行效能優於使用預設 CE，請移至下一個步驟。 如果效能未改善，CE 變更不是原因。
步驟 3：了解查詢為何使用舊版 CE 執行得更好
測試查詢的各種 CE 相關 查詢提示 。 針對 SQL Server 2014，請使用對應的追蹤旗標 4137、 9472 和 4139 來測試查詢。 根據這些測試判斷哪些提示或追蹤旗標會對效能產生正面影響。
解決方法
若要解決此問題，請嘗試下列其中一個方法：
優化查詢。
可以理解的是，不一定可以重寫查詢，但特別是只有少數可以重寫的查詢時，此方法應該是第一選擇。 無論 CE 版本為何，以最佳方式撰寫的查詢都會執行得更好。
使用步驟 3 中所識別的查詢提示。
此目標方法可讓其他工作負載受益於預設 CE 假設和改進。 此外，這是比建立計劃指南更健全的選項。 它不需要 查詢存放區（QDS），不像強迫計劃（最健全的選擇）。
強制制定好計劃。
這是一個有利的選項，可用來以特定查詢為目標。 您可以使用計劃指南或 QDS 來強制執行計劃。 QDS 通常更容易使用。
使用 資料庫範圍的組態 來強制使用舊版CE。
這是較不慣用的方法，因為它是全資料庫設定，並套用至此資料庫的所有查詢。 不過，當目標方法不可行時，有時是必要的。 實作最簡單的選項肯定是最簡單的選項。
使用追蹤旗標 9841 強制全球舊版 CE。 若要這樣做，請使用 DBCC TRACEON 或將追蹤旗標設定為 啟動參數。
這是最不具目標的方法，只有在您無法套用任何其他選項時，才應該做為暫時緩和措施。
啟用舊版 CE 的選項
查詢層級：使用查詢提示或 QUERYTRACEON 選項
針對 SQL Server 2016 SP1 和更新版本，請使用提示
FORCE_LEGACY_CARDINALITY_ESTIMATION進行查詢，例如：SELECT * FROM Table1 WHERE Col1 = 10 OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));啟用追蹤旗標 9481 以強制使用舊版 CE 方案。 以下是範例：
SELECT * FROM Table1 WHERE Col1 = 10 OPTION (QUERYTRACEON 9481)
資料庫層級：設定範圍組態或相容性層級
針對 SQL Server 2016 和更新版本，請改變資料庫範圍設定：
--Force a specific database to use legacy CE ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; -- Validate what databases use legacy CE SELECT name, value FROM sys.database_scoped_configurations WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';改變資料庫的相容性層級。 這是 SQL Server 2014 唯一可用的資料庫層級選項。 請注意，這項變更只會影響CE。 若要判斷相容性層級變更的影響，請移至 ALTER DATABASE 相容性層級 （Transact-SQL） 並檢查其中的「差異」數據表。
ALTER DATABASE <YourDatabase> SET COMPATIBILITY_LEVEL = 110 -- set it to SQL Server 2012 level
注意
除非使用覆寫追蹤旗標或查詢提示，否則這項變更會影響在變更組態之資料庫內容內執行的所有查詢。 由於預設 CE 而效能較佳的查詢可能會回歸。
伺服器層級：使用追蹤旗標
使用追蹤旗標 9481 強制全伺服器舊版 CE：
--Turn on
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS
注意
除非使用覆寫追蹤旗標或查詢提示，否則這項變更會影響在 SQL Server 實例內容內執行的所有查詢。 由於預設 CE 而效能較佳的查詢可能會回歸。
常見問題集
問1：我有興趣升級至較新版本的 SQL Server，而我擔心基數估算器效能回歸。 建議針對將問題降到最低的建議升級規劃為何？
對於在較低相容性層級執行的預先存在資料庫，建議的工作流程會將查詢處理器升級至較高的相容性層級，詳述於變更資料庫相容性模式和使用 查詢存放區 和使用案例 查詢存放區。 本文中介紹的方法適用於 SQL Server 和 Azure SQL 資料庫 的移至 130 或更新版本。
問 2：我沒有時間測試 CE 變更。 在此情況下，我該怎麼做？
對於預先存在的應用程式和工作負載，我們不建議移至預設CE，直到執行足夠的回歸測試為止。 如果您仍然有疑問，建議您仍升級 SQL Server 並移至最新的可用相容性層級。 作為預防措施，也啟用 SQL Server 2014 的追蹤旗標 9481，或為 SQL Server 2016 和更新版本設定LEGACY_CARDINALITY_ESTIMATION資料庫範圍ON設定，直到您有機會進行測試為止。
問 3：使用舊版 CE 是否有任何缺點？
未來的基數估計工具相關改善和修正以較新版本為中心。 版本 70 是可接受的中繼狀態。 不過，經過仔細測試之後，我們建議最終移至較新的 CE 版本，以受益於最新的 CE 修正。 從舊版 CE 移動時，查詢計劃變更的可能性很高，因此在變更生產系統之前先進行測試。 這些變更在許多情況下可以改善查詢效能，但在某些情況下，查詢效能可能會降低。
重要
默認 CE 是主要程式代碼路徑，將在未來進行長期投資和更深入的測試涵蓋範圍，因此請勿無限期地規劃使用舊版 CE。
問 4：我有數千個資料庫，而且不想針對每個資料庫手動開啟LEGACY_CARDINALITY_ESTIMATION。 是否有替代方法？
針對 SQL Server 2014，請啟用追蹤旗標 9481，以針對所有資料庫使用舊版 CE，而不論相容性層級為何。 針對 SQL Server 2016 和更新版本，請執行下列查詢逐一查看資料庫。 即使在另一部伺服器中還原或附加資料庫時，也會啟用此設定。
SELECT [name], 0 AS [isdone]
INTO #tmpDatabases
FROM master.sys.databases WITH (NOLOCK)
WHERE database_id > 4 AND source_database_id IS NULL AND is_read_only = 0
DECLARE @dbname sysname, @sqlcmd NVARCHAR(500);
WHILE (SELECT COUNT([name]) FROM #tmpDatabases WHERE isdone = 0) > 0
BEGIN
SELECT TOP 1 @dbname = [name] FROM #tmpDatabases WHERE isdone = 0
SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + ';
IF (SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''LEGACY_CARDINALITY_ESTIMATION'') = 0
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;'
BEGIN TRY
EXECUTE sp_executesql @sqlcmd
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState, ERROR_MESSAGE() AS ErrorMessage;
END CATCH
UPDATE #tmpDatabases
SET isdone = 1
WHERE [name] = @dbname
END;
針對 Azure SQL 資料庫，您可以建立支援票證，以在訂用帳戶層級啟用此追蹤旗標，但無法啟用伺服器層級。
問 5：使用舊版 CE 執行是否會防止我存取新功能？
即使啟用LEGACY_CARDINALITY_ESTIMATION，您仍然可以存取 SQL Server 版本和相關聯的資料庫相容性層級隨附的最新功能。 例如，在 SQL Server 2017 上啟用資料庫相容性層級 140 執行LEGACY_CARDINALITY_ESTIMATION的資料庫仍然可以受益於 調適型查詢處理 功能系列。
問 6：舊版 CE 何時會退出支援？
我們目前沒有計劃停止支援舊版CE。 不過，未來的基數估計工具相關改進和修正以較新版本的 CE 為中心。
問 7：我只有一些查詢會隨著預設 CE 回歸，但大部分的查詢效能都相同，甚至改善。 我該怎麼做？
伺服器範圍追蹤旗標 9481 或LEGACY_CARDINALITY_ESTIMATION資料庫範圍組態的更細微替代方案是使用查詢範圍的 USE HINT 建構。 如需詳細資訊，請參閱 SQL Server 2016 和 USE HINT 中的 USE HINT 查詢提示自變數。
注意
也有追蹤 QUERYTRACEON 旗標 9481 的選項，但您應該考慮改用 USE HINT ，因為它在語意上更簡潔，而且不需要特殊許可權。
USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION 可讓您將查詢優化器 CE 模型設定為版本 70，而不論資料庫的相容性層級為何。 請參閱 查詢層級：使用查詢提示或QUERYTRACEON選項。
或者，如果只有一個查詢與預設 CE 有問題，您可以強制儲存在 查詢存放區 中的舊版 CE 方案，或搭配計劃指南使用FORCE_LEGACY_CARDINALITY_ESTIMATION。
問8：如果使用預設CE時，如果因計劃變更而嚴重超過或估計不足而回歸查詢效能，產品中是否會修正此問題？
CE 是一個複雜的問題，而且演算法依賴用於估計的不完美數據，例如數據表和索引的統計數據。 根據許多假設（例如述詞和數據行的相互關聯或獨立性、統一數據分佈、內含專案等等），某些模型外建構沒有資訊，例如數據表值函式 （TVF） 和模型。
鑒於客戶架構、數據和工作負載的無限制組合，幾乎不可能挑選適用於所有案例的模型。 雖然預設 CE 中的某些變更可能包含 Bug（就像任何其他軟體可以一樣），而且可以修正，但模型變更會造成其他問題。
CE 版本的變更，特別是從 70 到 120 的變更，包含許多不同的模型選擇。 例如，在估計篩選時，假設述詞之間有某種程度的相互關聯，因為實際上，這類相互關聯經常存在，而 CE 模型 70 會低估這類案例的結果。 雖然這些變更已針對許多工作負載進行測試，並改善了許多查詢，但對於某些其他查詢，舊版 CE 是較佳的比對，因此若是預設 CE，可能會觀察到效能回歸。
不幸的是，它不會被視為 Bug。 在這種情況下，請使用調整查詢等因應措施，就像您在查詢效能無法接受時需要處理舊版 CE 一樣，或強制先前的 CE 模型或特定執行計劃。
問9：是否有任何資源可了解預設CE中基數變更和查詢效能影響的詳細數據？
如需詳細資訊，請參閱 使用 SQL Server 2014 基數估算器 優化您的查詢計劃，並閱讀