基数估计 (SQL Server)

项目
01/08/2024

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例

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

查询计划每个级别上处理的总行数，称为该计划的基数。
由查询中所使用的运算符规定的算法的开销模式。

第一个因素（基数）用作第二个因素（开销模式）的输入参数。因此，增大基数将减少估计开销，从而加快执行计划。

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

在下列情况下，SQL Server 无法精确计算基数。这会导致成本计算不准确，进而可能导致查询计划欠佳。避免在查询中使用这些构造可能会提高查询性能。有时，使用查询表达式或其他措施也可以提高查询性能，如下所述：

带谓词的查询，这些查询在同一表的不同列之间使用比较运算符。
带谓词的查询，这些查询使用运算符且下列任何一种情况为 True：
- 运算符两侧所涉及的列中没有统计信息。
- 统计信息中值的分布不均匀，但查询将查找高选择性的值集。特别是，当运算符是除相等 (=) 运算符以外的任何其他运算符时，这种情况可能为 True。
- 谓词使用不等于 (!=) 比较运算符或 NOT 逻辑运算符。
使用任意 SQL Server 内置函数或标量值用户定义函数（其参数不是常量值）的查询。
包含通过算术或字符串串联运算符联接的列的查询。
比较在编译或优化查询时其值未知的变量的查询。

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

你的应用程序系统可能具有重要的查询，其计划由于 CE 在各版本中发生而更改为较慢计划。你可以使用技术和工具来识别因 CE 问题而执行变慢的查询。你也可以选择如何解决后续性能问题。

CE 的版本

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

独立性：假设不同列上的数据分布是独立于彼此的，除非可获取相关性信息且信息可用。
一致性：不同值均匀分布且具有相同的频率。更确切地说，是在每个直方图步骤中，不同值均匀分布，并且每个值都具有相同的频率。
包含（简单）：用户查询已存在的数据。例如，对于两个表之间的等值联接，在联接直方图以评估联接选择性之前，考虑每个输入直方图中的谓词选择性¹。
包含：对于 Column = Constant 的筛选器谓词，通常假定事实上关联列存在常数。如果相应的直方图步骤非空，则假定该步骤的其中一个不同值匹配谓词的值。

¹满足谓词的行计数。

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

独立性转变为相关性：不同列值的组合不一定独立。这可能类似于更真实的数据查询。
简单包含转变为基础包含：用户可能查询不存在的数据。例如，对于两个表之间的等值联接，我们使用基本表直方图来评估联接选择性，然后考虑谓词选择性因素。

使用查询存储评估 CE 版本

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

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

重要

确保为数据库和工作负载正确配置查询存储。有关详细信息，请参阅有关查询存储的最佳做法。

使用扩展事件评估 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

注意

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

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

评估 CE 版本的步骤

以下步骤可用于评估当使用最新 CE 时最重要的查询的执行是否变差。其中一些步骤通过运行上一节中提供的代码示例来执行。

打开 SQL Server Management Studio (SSMS)。确保将 SQL Server 数据库设为最高可用兼容性级别。
执行以下初始步骤：
1. 打开 SQL Server Management Studio (SSMS)。
2. 运行 Transact-SQL，确保将 SQL Server 数据库设为最高可用兼容性级别。
3. 确保数据库已关闭其 LEGACY_CARDINALITY_ESTIMATION 配置。
4. 清除查询存储。在数据库中，确保查询存储处于开启状态。
5. 运行语句：SET NOCOUNT OFF;
运行语句：SET STATISTICS XML ON;
运行重要的查询。
在结果窗格的“消息”选项卡上，记下实际受影响的行数。
在结果窗格的“结果”选项卡上，双击包含 XML 格式的统计信息的单元格。将显示图形查询计划。
在图形查询计划的第一个框中右键单击，然后选择“属性”。
针对后面的与不同配置的比较，请记下以下属性的值：
- CardinalityEstimationModelVersion。
- 估计的行数。
- 估计的 I/O 成本，以及一些涉及实际性能而不是行数预测的类似的估计属性。
- 逻辑操作 和 物理操作。 “并行”是一个不错的选择。
- 实际执行模式。 “批处理”是一个不错的选择，优于“行”。
将估计的行数与实际行数进行比较。 CE 的不准确率偏高或偏低 1% 还是 10%？
运行：SET STATISTICS XML OFF;
运行 Transact-SQL，将数据库的兼容性级别降低一个级别（例如从 130 降到 120）。
重新运行所有非初始步骤。
比较这两次运行的 CE 属性值。
- 最新 CE 下的不准确率是不是小于较旧 CE 下的不准确率？
最后，比较这两次运行中的各个性能属性值。
- 在这两个不同的 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，而无需更改查询。
通过查询存储强制执行其他计划。

数据库兼容性级别

通过使用以下 COMPATIBILITY_LEVEL 的 Transact-SQL 代码，可以确保数据库位于特定级别。

重要

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

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

注意

在更改数据库兼容性级别之前，请参阅升级数据库兼容性级别的最佳做法。

ALTER DATABASE <yourDatabase>  
SET COMPATIBILITY_LEVEL = 150;  
GO

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

旧版基数估计器

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

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'));

设置查询存储提示

借助查询存储提示，无需修改查询就可强制执行查询来使用旧版基数估算器。

在查询存储目录视图 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%';

以下示例应用查询存储提示来对 query_id 39 强制执行旧版基数估算器，没有修改查询：

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

注意

有关详细信息，请参阅查询存储提示（预览版）。目前，此功能仅可用于 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 认为最大值可能大于最近收集统计信息时的值

当 OrderAddedDate 的最大值为 2016-04-30 时，假定上次在 2016-04-30 收集 OrderTable 的统计信息。 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';

基数估计 (SQL Server)

CE 的版本

使用查询存储评估 CE 版本

使用扩展事件评估 CE 版本

评估 CE 版本的步骤

如何激活最佳查询计划

数据库兼容性级别

旧版基数估计器

修改查询来使用提示

设置查询存储提示

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

基数估计过程中的常量折叠和表达式计算

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

CE 改进示例

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

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

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

反馈

反馈

其他资源

基数估计 (SQL Server)

CE 的版本

使用查询存储评估 CE 版本

使用扩展事件评估 CE 版本

评估 CE 版本的步骤

如何激活最佳查询计划

数据库兼容性级别

旧版基数估计器

修改查询来使用提示

设置查询存储提示

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

基数估计过程中的常量折叠和表达式计算

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

CE 改进示例

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

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

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

相关内容

反馈

反馈

其他资源