sql 5120 基数估计 (SQL Server)

sql 5120 基数估计 (SQL Server)

适用于：

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

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

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

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

使用任意 SQL 内置函数或标量值用户定义函数（其参数不是常量值）的查询。包含通过算术或字符串串联运算符联接的列的查询。比较在编译或优化查询时其值未知的变量的查询。

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

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

CE 的版本

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

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

使用查询存储评估 CE 版本

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

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

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

使用扩展事件评估 CE 版本

跟踪基数估计过程的另一种方法是使用名为 的扩展事件。 以下 -SQL 代码示例在 SQL 上运行。 它将 .xel 文件写入 C:Temp（尽管可以更改路径）。 在 中打开此 .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:tempxe_qoec_1.xel',  
  metadatafile = 'c:tempxe_qoec_1.xem'  
 );  
GO  
  
ALTER EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
STATE = START;  --STOP;  
GO  

Note

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

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

评估 CE 版本的步骤

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

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

执行以下初始步骤：

打开 SQL (SSMS)。

运行 -SQLsql 5120 基数估计 (SQL Server)，确保将 SQL 数据库设为最高可用兼容性级别。

确保数据库已关闭其 配置。

清除查询存储。 在数据库中，确保。

运行语句：SET OFF;

运行语句：SET XML ON;

运行重要的查询。

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

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

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

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

将估计的行数与实际行数进行比较。 CE 的不准确率偏高或偏低 1% 还是 10%？

运行：SET XML OFF;

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

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

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

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

如何激活最佳查询计划

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

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

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

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

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

数据库兼容性级别

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

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

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

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

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

ALTER DATABASE   
SET COMPATIBILITY_LEVEL = 150;  
GO  

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

旧版基数估计器

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

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 2016 (13.x) SP1 开始，可修改查询来使用USE HINT (”)。

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

设置查询存储提示

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

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

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

以下示例应用查询存储提示来对 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。 该方法将在后面详细说明。

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

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

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

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

此外，有些不可进行常量折叠但其参数在编译时已知的表达式，无论其参数是形参还是常量，都将由优化期间查询优化器中包括的结果集大小（基数）估计器进行计算。 有关详细信息，请参阅。

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

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

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

CE 改进示例

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

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

当 的最大值为 2016-04-30 时，假定上次在 收集 2016-04-30 的统计信息。 CE 120（和更高版本）认为数据按升序排序的 中的列的值可能大于由统计信息记录的最大值。 这种假设改进了 -SQL 语句的查询计划，如下所示。

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

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

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

从 CE 120 开始，SQL 认为同一表中 Model 和 两个列之间存在相关性。 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';  

相关内容