从 SQL Server 2012 或更早版本升级到 2014 或更高版本后，查询性能下降

将 SQL Server 从 2012 或更低版本升级到 2014 或更高版本后，可能会遇到以下问题：大多数原始查询运行良好，但一些查询的运行速度比以前的版本慢。 虽然有许多可能的原因和促成因素，但一个相对常见的原因是升级后 基数估计 (CE) 模型的变化。 从 2014 SQL Server 开始，CE 模型发生了重大变化。

本文提供有关使用默认 CE 时发生的查询性能问题的故障排除步骤和解决方法，但在使用旧版 CE 时不会发生这些问题。

注意

如果所有查询在升级后运行速度较慢，本文中介绍的故障排除步骤可能不适用于你的情况。

故障排除：确定 CE 更改是否是问题并找出原因

步骤 1：确定是否使用默认 CE

1. 选择升级后运行速度较慢的查询。
2. 运行查询并 收集执行计划。
3. 从执行计划属性窗口，检查 CardinalityEstimationModelVersion。
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 的影响。 若要确定兼容性级别更改的影响，请转到 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 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 是main代码路径，它将获得未来投资和长期更深入的测试覆盖范围，因此不要计划无限期地使用旧版 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 版本中包含的最新功能以及关联的数据库兼容性级别。 例如，在 2017 SQL Server 2017 上，启用了 LEGACY_CARDINALITY_ESTIMATION 的数据库在数据库兼容性级别 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与计划指南结合使用。

问题 8：如果使用默认 CE 时，如果由于计划更改而导致查询性能下降，则问题是否会在产品中得到修复？

CE 是一个复杂的问题，算法依赖于可用于估计的不太完美的数据，例如表和索引的统计信息。 一些模型外构造（如表值函数 (TVF) 和模型）没有信息，这些假设 (例如谓词和列的相关性或独立性、统一数据分布、包含等) 。

鉴于客户架构、数据和工作负载的无限组合，几乎不可能选择适用于所有情况的模型。 虽然默认 CE 中的某些更改可能包含 bug (，就像任何其他软件可以) 并且可以修复一样，但其他问题是由模型更改引起的。

CE 版本的更改（尤其是从 70 到 120）包含许多不同的模型选项。 例如，在估算筛选器时，假设谓词之间存在某种程度的相关性，因为在实践中，这种相关性经常存在，CE 模型 70 会低估此类情况下的结果。 虽然这些更改针对许多工作负载进行了测试并改进了许多查询，但对于其他一些查询，旧版 CE 是更好的匹配项，因此在默认 CE 中，可能会观察到性能回归。

遗憾的是，它不被视为 bug。 在这种情况下，请使用一种解决方法，例如优化查询，就像在查询性能不可接受的情况下使用旧版 CE 一样，或者强制使用以前的 CE 模型或特定执行计划。

问题 9：是否有任何资源可以详细了解默认 CE 中的基数更改和查询性能影响？

有关详细信息，请参阅使用 SQL Server 2014 基数估算器优化查询计划，并阅读“SQL Server 2014 有哪些更改？”部分。