基数估计 (SQL Server)

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Microsoft Fabric 中的 SQL 数据库

SQL Server 查询优化器是基于成本的查询优化器。 也就是说，它选择估计处理成本最低的查询计划。 查询优化器基于以下两个主要因素来确定执行查询计划的开销：

  • 查询计划每个级别上处理的总行数，称为该计划的基数。
  • 该算法的成本模型由查询中使用的运算符决定。

第一个因素（基数）用作第二个因素（开销模式）的输入参数。 因此，更准确的基数估计会带来更准确的成本估算，进而生成执行速度更快的执行计划。

SQL Server 中的基数估计 (CE) 主要派生自创建索引或统计信息时所创建的直方图（以手动或自动方式）。 有时，SQL Server 还使用查询的约束信息和逻辑重写来确定基数。

在下列情况下，SQL Server 无法精确计算基数。 这会导致成本计算不准确，进而可能导致查询计划欠佳。 避免在查询中使用这些构造可能会提高查询性能。 有时，也可以采用其他查询写法或其他方法，下面会指出这些做法：

  • 带谓词的查询，这些查询在同一表的不同列之间使用比较运算符。
  • 使用运算符的谓词查询，且以下任一条件为真：
    • 运算符两侧涉及的列均无统计信息。
    • 统计信息中值的分布不均匀，但查询将查找高选择性的值集。 如果运算符不是相等 (=) 运算符，而是其他任何运算符，那么这种情况尤其如此。
    • 谓词使用不等于 (!=) 比较运算符或 NOT 逻辑运算符。
  • 使用任意 SQL Server 内置函数或标量值用户定义函数（其参数不是常量值）的查询。
  • 涉及通过算术运算符或字符串连接运算符对列进行联接的查询。
  • 比较在编译或优化查询时其值未知的变量的查询。

本文将阐释如何评估和选择系统的最佳 CE 配置。 大多数系统受益于最新的 CE，因为它最准确。 CE 将预测查询可能返回的行数。 查询优化器使用基数预测来生成最佳查询计划。 通过更准确的估计，查询优化器通常可以更好地生成更优查询计划。

你的应用系统中可能存在某个重要查询，由于 CE 在不同版本中的变化，其执行计划可能会变成更慢的计划。 你可以使用技术和工具来识别因 CE 问题而执行变慢的查询。 你也可以选择如何解决后续性能问题。

CE 的版本

在 1998 年，CE 的重大更新是 SQL Server 7.0 的一部分，其兼容性级别为 70。 此版本的 CE 模型建立在四个基本假设之上：

  • 独立性：假设不同列上的数据分布是独立于彼此的，除非可获取相关性信息且信息可用。

  • 一致性：不同值均匀分布且具有相同的频率。 更确切地说，是在每个直方图步骤中，不同值均匀分布，并且每个值都具有相同的频率。

  • 包含（简单）：用户查询已存在的数据。 例如，对于两个表之间的等值联接，在联接这些直方图以估算联接选择性之前，应先将每个输入直方图中谓词1的选择性纳入考虑。

  • 包含性：对于其中 Column = Constant 的筛选谓词，假定该常量确实存在于对应列中。 如果相应的直方图步骤非空，则假定该步骤的其中一个不同值匹配谓词的值。

    1满足谓词的行计数。

后续更新从 SQL Server 2014 (12.x) 开始，意味着兼容性级别为 120 及以上。 级别 120 及以上的 CE 更新中引入了已更新的假设和算法，非常适用于现代数据仓库和 OLTP 工作负荷。 从 CE 70 假设开始，以下模型假设已自 CE 120 起更改：

  • 独立 成为 相关性： 不同列值的组合不一定是独立的。 这可能类似于更真实的数据查询。
  • 简单包含 成为 基本包含： 用户可以查询不存在的数据。 例如，对于两个表之间的等值连接，我们使用基表的直方图来估算连接选择率，然后再将谓词的选择率纳入考虑。

使用查询存储来评估 CE 版本

从 SQL Server 2016 (13.x) 开始，可使用查询存储轻松检查查询性能。 启用查询存储后，它将开始跟踪一段时间的查询性能，即使执行计划更改也是如此。 监视 查询存储 中高成本或性能回退的查询。 有关更多信息，请参阅 使用查询存储监视性能。

如果准备升级到 SQL Server 或在任何 SQL Server 平台中提升数据库兼容性级别，请考虑 使用查询优化助手升级数据库，这有助于比较两个不同的兼容级别的查询性能。

Important

确保针对您的数据库和工作负载正确配置查询存储。 有关详细信息，请参阅 使用查询存储监视工作负荷的最佳做法。

使用扩展事件评估 CE 版本

跟踪基数估计过程的另一种方法是使用名为 query_optimizer_estimate_cardinality 的扩展事件。 以下 Transact-SQL 代码示例在 SQL Server 上运行。 它将 .xel 文件写入 C:\Temp\（尽管可以更改路径）。 在 Management Studio 中打开此 .xel 文件时，其详细信息将以用户友好的方式显示。

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;
go

CREATE EVENT SESSION Test_the_CE_qoec_1
ON SERVER
ADD EVENT sqlserver.query_optimizer_estimate_cardinality
 (
 ACTION (sqlserver.sql_text)
  WHERE (
  sql_text LIKE '%yourTable%'
  and sql_text LIKE '%SUM(%'
  )
 )
ADD TARGET package0.asynchronous_file_target
 (SET
  filename = 'c:\temp\xe_qoec_1.xel',
  metadatafile = 'c:\temp\xe_qoec_1.xem'
 );
GO

ALTER EVENT SESSION Test_the_CE_qoec_1
ON SERVER
STATE = START;  --STOP;
GO

Note

该事件 sqlserver.query_optimizer_estimate_cardinality 不适用于 Azure SQL 数据库。

有关为 SQL 数据库定制的扩展事件的信息，请参阅 SQL 数据库中的扩展事件。

评估 CE 版本的步骤

接下来介绍一些步骤，可用于评估你最重要的一些查询在最新 CE 下是否出现了性能下降。 其中一些步骤通过运行上一节中提供的代码示例来执行。

  1. 打开 SQL Server Management Studio (SSMS)。 确保将 SQL Server 数据库设为最高可用兼容性级别。

  2. 执行以下初始步骤：

    1. 打开 SQL Server Management Studio (SSMS)。

    2. 运行 Transact-SQL，确保将 SQL Server 数据库设为最高可用兼容性级别。

    3. 确保数据库的LEGACY_CARDINALITY_ESTIMATION配置已OFF启用。

    4. 清空查询存储。 在数据库中，确保查询存储处于开启状态。

    5. 运行以下语句：SET NOCOUNT OFF;

  3. 运行以下语句：SET STATISTICS XML ON;

  4. 运行重要的查询。

  5. 在结果窗格的“消息”选项卡上，记下实际受影响的行数。

  6. 在结果窗格的“结果”选项卡上，双击包含 XML 格式的统计信息的单元格。 将显示图形查询计划。

  7. 在图形查询计划的第一个框中右键单击，然后选择“属性”。

  8. 针对后面的与不同配置的比较，请记下以下属性的值：

    • CardinalityEstimationModelVersion.

    • 估计的行数。

    • 估计 I/O 成本，以及其他几个类似的估计属性，这些属性涉及实际性能，而不是行数预测。

    • 逻辑操作 和 物理操作。 并行性是一个不错的值。

    • 实际执行模式。 批处理是一个不错的值，优于行。

  9. 将估计的行数与实际行数进行比较。 CE 的偏差是 1%（偏高或偏低），还是 10%？

  10. 运行: SET STATISTICS XML OFF;

  11. 运行 Transact-SQL，将数据库的兼容性级别降低一个级别（例如从 130 降到 120）。

  12. 重新运行所有非初始步骤。

  13. 比较这两次运行的 CE 属性值。

    • 最新 CE 下的不准确率是不是小于较旧 CE 下的不准确率？

  14. 最后，比较这两次运行中的各个性能属性值。

    • 你的查询在这两种不同的 CE 估算下是否使用了不同的执行计划？

    • 在最新 CE 下你的查询是否运行较缓慢？

    • 除非你的查询在旧版 CE 下运行效果更好，并且采用不同的执行计划，否则你几乎肯定应该使用最新的 CE。

    • 但是，如果你的查询在较旧的 CE 下使用更快的执行计划运行，可以考虑强制系统使用该更快的执行计划并忽略 CE。 这样一来，你就可以让所有情况都使用最新的 CE，同时在那一个特殊情况下保留较快的计划。

如何激活最佳查询计划

假设在使用 CE 120 的或更高版本情况下，针对查询生成了效率较低的查询计划。 下面是一些可用于激活最佳计划的选项，这些选项按从最大范围到最小范围排序：

  • 对于整个数据库，可将数据库兼容性级别设置为低于最新可用级别的值。

    • 例如，将兼容性级别设置为 110 或更低会激活 CE 70，但这会使所有查询都受制于以前的 CE 模型。

    • 此外，如果设置较低的兼容性级别，还会遗漏最新版本中的大量查询优化器改进。并会影响所有针对数据库的查询。

  • 可以使用 LEGACY_CARDINALITY_ESTIMATION 数据库范围配置选项，使整个数据库使用旧版 CE，同时保留查询优化器中的其他改进。

  • 可以使用 LEGACY_CARDINALITY_ESTIMATION 查询提示，让单个查询使用较旧 CE，同时保留查询优化器中的其他改进。

  • 可通过查询存储提示功能来强制执行 LEGACY_CARDINALITY_ESTIMATION，使单个查询使用较旧的 CE，而无需更改查询。

  • 通过查询存储强制执行其他计划。

数据库兼容性级别

对于 （Transact-SQL） 兼容级别，可以使用以下 Transact-SQL 代码ALTER DATABASE来确保数据库处于特定级别。

Important

SQL Server 和 Azure SQL 数据库的数据库引擎版本号彼此之间不可比，而是这两种不同产品各自的内部版本号。 适用于 Azure SQL Server 的数据库引擎与 SQL Server 数据库引擎基于相同的代码库。 最重要的是，Azure SQL 数据库中的数据库引擎始终具有 SQL 数据库引擎的最新功能。 Azure SQL 数据库版本 12 比 SQL Server 版本 15 更新。 从 2019 年 11 月起，在 Azure SQL 数据库 中，新创建的数据库的默认兼容性级别为 150。 Microsoft不会更新现有数据库的数据库兼容性级别。 由客户自行决定。

SELECT ServerProperty('ProductVersion');
GO

SELECT d.name, d.compatibility_level
FROM sys.databases AS d
WHERE d.name = 'yourDatabase';
GO

对于在较低兼容级别运行的预先存在的数据库，只要应用程序不需要使用仅在较高数据库兼容性级别中可用的增强功能，那么维护以前的数据库兼容性级别是一种有效的方法。 对于新的开发工作，或者当现有应用程序需要使用新功能（例如 SQL 数据库中的智能查询处理）以及一些新的 Transact-SQL 时，计划将数据库兼容性级别升级到最新的可用版本。 有关详细信息，请参阅兼容性级别和数据库引擎升级。

Caution

在更改数据库兼容性级别之前，请查看 ALTER DATABASE （Transact-SQL）兼容性级别。

ALTER DATABASE <yourDatabase>
SET COMPATIBILITY_LEVEL = 150;
GO

对于在兼容级别 120 及以上设置的 SQL Server 数据库，激活跟踪标志 9481 会强制系统使用 CE 版本 70。

旧版基数估计器

对于兼容级别设置为 120 及以上的 SQL Server 数据库，可以通过使用 ALTER DATABASE SCOPED CONFIGURATION 在数据库级别激活旧版基数估算器（CE 版本 70）。

ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO

SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
GO

修改查询以使用提示

从 SQL Server 2016 (13.x) SP1 开始，可修改查询来使用查询提示USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')。

SELECT CustomerId, OrderAddedDate
FROM OrderTable
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

设置查询存储提示

可以使用查询存储提示，在不修改查询的情况下强制查询使用旧版基数估算器。

  1. 在查询存储目录视图 sys.query_store_query_text 和 sys.query_store_query 中标识查询。 例如，按文本片段搜索已执行的查询：

    SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'
AND query_sql_text not like N'%query_store%';

  2. 以下示例演示如何使用 查询存储 提示对 query_id 39 强制使用旧版基数估算器，而无需修改查询：

    EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

Note

有关详细信息，请参阅 查询存储提示 （预览版）。 目前，此功能仅可用于 Azure SQL 数据库。

如何强制使用特定的查询计划

为了实现更好的控制，可以强制系统在测试期间使用通过 CE 70 生成的计划。 固定首选计划后，可以将整个数据库设置为使用最新兼容性级别和 CE。 该方法将在后面详细说明。

查询存储提供了不同方式来强制系统使用特定的查询计划：

  • 执行 sys.sp_query_store_force_plan。

  • 在 SQL Sever Management Studio (SSMS) 中，展开“查询存储”节点，右键单击“资源使用排名靠前的节点”，然后选择“查看资源使用排名靠前的节点”。 界面显示标有 “强制计划” 和 “取消强制计划” 的按钮。

有关查询存储的详细信息，请参阅 使用查询存储监视性能。

基数估计期间的常量折叠和表达式求值

数据库引擎会先计算一些常数表达式来提高查询性能。 这称作常量折叠。 常数是 Transact-SQL 文本，例如 3、'ABC'、'2005-12-31'、1.0e3 或 0x12345678。 有关详细信息，请参阅常量折叠。

此外，对于某些不会进行常量折叠、但其参数在编译时已知的表达式，无论这些参数是参数还是常量，在优化过程中，查询优化器中的结果集大小（基数）估算器都会对其进行求值。 有关详细信息，请参阅表达式计算。

最佳做法：使用常量折叠和编译时表达式计算来生成最佳查询计划

若要确保生成最佳查询计划，最好设计查询、存储过程和批处理，使查询优化器可根据数据分布统计信息准确估计查询中条件的选择性。 否则，在估计选择性时，查询优化器必须使用默认估计值。

若要确保查询优化器的基数估算器提供良好的估计值，应首先确保 AUTO_CREATE_STATISTICS 和 AUTO_UPDATE_STATISTICS 数据库 SET 选项为 ON （默认设置），或者已手动创建查询条件中引用的所有列的统计信息。 然后，在设计查询条件时，尽可能遵循下列准则：

  • 避免在查询中使用局部变量。 而是在查询中使用参数、文本或表达式。

  • 将包含参数的查询中嵌入的运算符和函数的使用限制为“基数估计编译时表达式求值”中列出的运算符和函数。

  • 请确保查询条件中仅包含常量的表达式要么可进行常量折叠，要么可在编译时求值。

  • 如果必须使用局部变量对查询中使用的表达式求值，请考虑在与查询不同的作用域中求值。 例如，执行下列选项之一可能非常有用：

    • 将变量的值传递给包含要计算的查询的存储过程，并让查询使用过程参数而不是局部变量。

    • 构造一个字符串，使其包含部分基于局部变量的值的查询，然后使用动态 SQL（EXEC，首选 sp_executesql）执行该字符串。

    • 将查询参数化、使用 sp_executesql 执行该查询，并将变量的值作为参数传递给该查询。

CE 改进示例

本节介绍了从最新版本的 CE 中实施的改进中获益的示例查询。 这是背景信息，不需要你的具体操作。

示例 A. CE 认为最大值可能大于最近收集统计信息时的值

当 OrderTable 的最大值为 2016-04-30 时，假定上次在 OrderAddedDate 收集 2016-04-30 的统计信息。 CE 120（及更高版本）会识别到，OrderTable 中包含升序数据的列，其值可能大于统计信息中记录的最大值。 此理解改进了 Transact-SQL SELECT 语句的查询计划，如以下语句。

SELECT CustomerId, OrderAddedDate
FROM OrderTable
WHERE OrderAddedDate >= '2016-05-01';

示例 B. CE 认为同一表上的筛选谓词通常是相关的

在下面的 SELECT 中，我们可以看到作用于 Model 和 ModelVariant 的筛选谓词。 我们直观地了解到，当 Model 是“Xbox”时，ModelVariant 有可能是“One”，因为 Xbox 有一个名为 One 的变体。

从 CE 120 开始，SQL Server 认为同一表中 Model 和 ModelVariant 两个列之间存在相关性。 CE 对于查询将返回多少行进行更准确的估计，并且查询优化器将生成更优的计划。

SELECT Model, Purchase_Price
FROM dbo.Hardware
WHERE Model = 'Xbox' AND
ModelVariant = 'Series X';

示例 C. CE 不再假设不同表的筛选谓词之间存在任何相关性

关于现代工作负载和实际业务数据的大量最新研究表明，来自不同表的谓词过滤条件通常彼此不相关。 在下面的查询中，CE 假设 s.type 与 r.date 之间没有关联。 因此，CE 对于返回的行数有一个偏低的估计值。

SELECT s.ticket, s.customer, r.store
FROM dbo.Sales AS s
CROSS JOIN dbo.Returns AS r
WHERE s.ticket = r.ticket AND
s.type = 'toy' AND
r.date = '2016-05-11';

相关内容

  • Last updated on