从 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 基数估算器 优化查询计划，并阅读“SQL Server 2014 中的哪些更改？” 部分。