适用于:是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库
本文将阐释如何评估和选择 SQL 系统的最佳基数估计 (CE) 配置。 大多数系统受益于最新的 CE,因为它最准确。 CE 将预测查询可能返回的行数。 查询优化器使用基数预测来生成最佳查询计划。 通过更准确的估计,查询优化器通常可以更好地生成更优查询计划。
你的应用程序系统可能具有重要的查询,其计划由于新的 CE 而更改为较慢计划。 此类查询可能为以下任一种:
- OLTP(联机事务处理)查询,由于该查询运行相当频繁,因此它的多个实例经常同时运行。
- 在 OLTP 工作期间运行的具有大量聚合函数的 SELECT 查询。
你可以使用技术来识别因新的 CE 而执行变慢的查询。 你也可以选择如何解决此性能问题。
CE 的版本
在 1998 年,CE 的重大更新是 SQL Server 7.0 的一部分,其兼容级别为 70。 此版本的 CE 模型建立在四个基本假设之上:
- 独立性: 假设不同列上的数据分布是独立于彼此的,除非可获取相关性信息且信息可用。
- 一致性: 不同值均匀分布且具有相同的频率。 更确切地说,是在每个直方图步骤中,不同值均匀分布,并且每个值都具有相同的频率。
- 包含(简单): 用户查询已存在的数据。 例如,对于两个表之间的等值联接,在联接直方图以评估联接选择性之前,考虑每个输入直方图中的谓词选择性<sup>1</sup>。
- 包含: 对于
Column = Constant的筛选器谓词,通常假定事实上关联列存在常数。 如果相应的直方图步骤非空,则假定该步骤的其中一个不同值匹配谓词的值。
<sup>1</sup>满足谓词的行计数。
后续更新从 SQL Server 2014 (12.x) 开始,意味着兼容性级别为 120 及以上。 级别 120 及以上的 CE 更新中引入了已更新的假设和算法,非常适用于现代数据仓库和 OLTP 工作负荷。 从 CE 70 假设开始,以下模型假设已自 CE 120 起更改:
- 独立性转变为相关性:不同列值的组合不一定独立。 这可能类似于更真实的数据查询。
- 简单包含转变为基础包含:用户可能查询不存在的数据。 例如,对于两个表之间的等值联接,我们使用基本表直方图来评估联接选择性,然后考虑谓词选择性因素。
兼容性级别: 通过使用以下 COMPATIBILITY_LEVEL 的 Transact-SQL 代码,可以确保数据库位于特定级别。
SELECT ServerProperty('ProductVersion');
GO
ALTER DATABASE <yourDatabase>
SET COMPATIBILITY_LEVEL = 130;
GO
SELECT d.name, d.compatibility_level
FROM sys.databases AS d
WHERE d.name = 'yourDatabase';
GO
对于在兼容级别 120 及以上设置的 SQL Server 数据库,激活跟踪标志 9481 会强制系统使用 CE 版本 70。
旧版 CE: 对于在兼容级别 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'));
查询存储:从 SQL Server 2016 (13.x) 开始,查询存储是用于检查查询性能的一种方便的工具。 在 Management Studio 中的数据库节点下的对象资源管理器中,当查询存储启用时,显示“查询存储”节点。
ALTER DATABASE <yourDatabase>
SET QUERY_STORE = ON;
GO
SELECT q.actual_state_desc AS [actual_state_desc_of_QueryStore],
q.desired_state_desc,
q.query_capture_mode_desc
FROM sys.database_query_store_options AS q;
GO
ALTER DATABASE <yourDatabase>
SET QUERY_STORE CLEAR;
Tip: 建议安装最新版本的 Management Studio 并且经常更新。
跟踪基数估计过程的另一种方法是使用名为 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
有关为 SQL Database 定制的扩展事件的信息,请参阅 SQL 数据库中的扩展事件。
评估 CE 版本的步骤
以下步骤用于评估当使用最新 CE 时最重要的查询的执行是否不佳。 其中一些步骤通过运行上一节中提供的代码示例来执行。
-
打开 Management Studio。 确保将 SQL Server 数据库设为最高可用兼容性级别。
-
执行以下初始步骤:
-
打开 Management Studio。
-
运行 T-SQL,确保将 SQL Server 数据库设为最高可用兼容性级别。
-
确保数据库已关闭其
LEGACY_CARDINALITY_ESTIMATION配置。 -
清除查询存储。 当然,要确保查询存储已打开。
-
运行语句:
SET NOCOUNT OFF;
-
-
运行语句:
SET STATISTICS XML ON; -
运行重要的查询。
-
在结果窗格的“消息”选项卡上,记下实际受影响的行数。
-
在结果窗格的“结果”选项卡上,双击包含 XML 格式的统计信息的单元格。 将显示图形查询计划。
-
在图形查询计划的第一个框中右键单击,然后单击“属性”。
-
针对后面的与不同配置的比较,请记下以下属性的值:
-
CardinalityEstimationModelVersion。
-
估计的行数。
-
估计的 I/O 成本,以及一些涉及实际性能而不是行数预测的类似的估计属性。
-
逻辑操作和物理操作。 “并行”是一个不错的选择。
-
实际执行模式。 “批处理”是一个不错的选择,优于“行”。
-
-
将估计的行数与实际行数进行比较。 CE 的不准确率偏高或偏低 1% 还是 10%?
-
运行:
SET STATISTICS XML OFF; -
运行 T-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,同时保留查询优化器中的其他改进。
为了实现更好的控制,可以强制系统在测试期间使用通过 CE 70 生成的计划。 固定首选计划后,可以将整个数据库设置为使用最新兼容性级别和 CE。 该方法将在后面详细说明。
如何强制使用特定的查询计划
查询存储提供了不同方式来强制系统使用特定的查询计划:
-
执行 sp_query_store_force_plan。
-
在 Management Studio 中,展开“查询存储”节点,右键单击“资源使用排名靠前的节点”,然后单击“查看资源使用排名靠前的节点”。 此时将显示“强制使用计划”和“取消强制使用计划”按钮。
有关查询存储的详细信息,请参阅 《Monitoring Performance By Using the Query Store》(使用查询存储监控性能)。
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 = 'One';
示例 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 2014 基数估算器优化查询计划
查询提示
USE HINT 查询提示
相关视图、函数和过程
查询处理体系结构指南
历史资料。此文对应 2018-11-30 的中文文档快照,适用产品版本和命令行为以原文为准。
来源与署名:Microsoft 和文档贡献者(原始作者元数据 MikeRayMSFT)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站展开固定版本仓库内的引用内容,将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。
