從 SQL Server 2012 或更早版本升級至 2014 或更新版本之後，查詢效能降低

  • 發行項

將 SQL Server 從 2012 或較舊的版本升級至 2014 或更新版本之後，您可能會遇到下列問題：大部分的原始查詢執行良好，但有些查詢的執行速度比舊版慢。 雖然有許多可能的原因和因素，但其中一個相對常見的原因是基 數估計 (CE) 模型在升級后所做的變更。 從 2014 年 SQL Server 開始，CE 模型已引進重大變更。

本文針對使用預設 CE 時所發生的查詢效能問題提供疑難解答步驟和解決方法，但在使用舊版 CE 時不會發生此問題。

注意事項

如果所有查詢在升級后執行速度較慢，本文中導入的疑難解答步驟可能不適用於您的情況。

疑難解答：識別 CE 變更是否為問題，並找出原因

步驟 1：識別是否使用預設 CE

  1. 選擇升級后執行速度較慢的查詢。
  2. 執行查詢並 收集執行計劃。
  3. 從執行計劃 屬性視窗，檢查 CardinalityEstimationModelVersion。 從執行計劃 屬性視窗 尋找 CE 模型版本。
  4. 值為 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';

  • 改變資料庫的相容性層級。 這是 2014 SQL Server 唯一可用的資料庫層級選項。 請注意，這項變更影響的不只是 CE。 若要判斷相容性層級變更的影響，請移至 Transact-SQL) (ALTER DATABASE 相容性層級 ，並檢查其中的「差異」數據表。

    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 而效能較佳的查詢可能會回歸。

常見問題集

對於在較低相容性層級執行的既有資料庫，將查詢處理器升級至較高相容性層級的建議工作流程詳述於變更資料庫相容性模式和使用 查詢存放區 和 查詢存放區 使用案例。 本文中介紹的方法適用於將 SQL Server 和 Azure SQL Database 移至 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 Database，您可以建立支援票證，以在訂用帳戶層級啟用此追蹤旗標，而不是在伺服器層級啟用此追蹤旗標。

問 5：使用舊版 CE 執行是否會阻止我存取新功能？

即使已啟用LEGACY_CARDINALITY_ESTIMATION，您仍然可以存取 SQL Server 版本和相關聯的資料庫相容性層級隨附的最新功能。 例如，已啟用LEGACY_CARDINALITY_ESTIMATION的資料庫在 2017 年 SQL Server 上的資料庫相容性層級 140 執行，仍然可以受益於自適性查詢處理功能系列。

問 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。

CE 是一個複雜的問題，而且演算法依賴較不完美的估計數據，例如數據表和索引的統計數據。 根據許多 (假設，如述詞和數據行的相互關聯或獨立性、統一數據散發、內含專案等) ，某些模型外建構沒有任何資訊，例如數據表值函式 (TVF) 和模型，如述詞和數據行的相互關聯或獨立性等許多假設。

基於客戶架構、數據和工作負載的無限制組合，幾乎不可能挑選適用於所有案例的模型。 雖然預設 CE 中的某些變更可能包含與任何其他軟體) 一樣 (的錯誤，而且可以修正，但其他問題則是由模型變更所造成。

CE 版本的變更，特別是從 70 到 120 的變更，包含許多用於模型的不同選擇。 例如，在估計篩選條件時，假設述詞之間有某種程度的相互關聯，因為實際上這類相互關聯經常存在，而 CE 模型 70 會在這類情況下略過結果。 雖然這些變更已針對許多工作負載進行測試，並改善了許多查詢，但對於其他一些查詢而言，舊版 CE 較相符，因此使用預設 CE 時，可能會觀察到效能回歸。

可惜的是，這不被視為 Bug。 在這種情況下，請使用像是調整查詢之類的因應措施，就如同您在無法接受查詢效能時處理舊版 CE，或強制執行先前的 CE 模型或特定執行計劃一樣。

問 9：是否有任何資源可深入瞭解預設 CE 中的基數變更和查詢效能影響？

See Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator for details and read the "What Changed in SQL Server 2014?" section.