SQL Server 2014新特性——基数评估（白皮书阅读笔记）

目录

基数评估... 1

说明... 2

基数评估准确的重要性... 2

模型假设... 3

启用新的基数评估... 3

验证基数评估的版本... 3

在迁移到新的基数评估前要测试... 4

校验基数评估... 4

偏差问题... 4

需要手动处理的变化... 4

避免因为新的CE造成性能下降... 4

SQL Server 2014中的修改... 5

增加多个谓词的相关性的假设... 5

修改超出统计信息范围的评估... 5

Join评估算法修改... 5

Join包含（Containment）假设的修改... 6

不同值计数评估的变化... 7

诊断输出... 7

新基数评估的调优方法... 8

修改数据库兼容级别... 8

使用跟踪标记... 8

基础调优方法... 8

查询优化器的目的是为了找出有效的执行计划，根据cost运算，取出cost最小的计划，作为执行计划。其中影响cost最重要的一项就是基数评估（估计行数）。SQL Server 2014对基数评估做了修改。

基数评估提供以下信息：

1.响应行数评估（the distribution of data）

2.不同值个数评估（distinct value count）

3.重复值个数，作为上一级基数评估（duplicate count as input for parent Operator estimation calculations）

基数评估是通过计算统计信息的出来的结果，而统计信息通过优化器创建或者通过索引创建。

统计信息分为：头，密度向量，直方图。

当统计信息存在的时候基数评估器使用密度向量和直方图来计算评估。

基数评估主要回答以下几个问题：

1.一个或多个谓词或过滤几行

2.2个表之间的连接谓词会过滤几行

3.预计一个指定列集合中有多少不同值（distinct value）

Sql server中有2种谓词：1.过滤谓词，2.连接谓词

基数评估（CE）：试图回答where，join，having这些谓词的选择性。也试图回答group，distinct的不同值（distinct value）。

CE的计算从图形执行计划中是从右到左的，下一级的评估作为上一级计算评估的输入。

每个执行计划中的运算符都有评估值输入，这个值决定了优化器使用什么算法的操作符，同时也决定了最终的执行计划。所以如果评估出现偏差，会导致执行计划选择出现偏差，导致无法选出一个高效的执行计划。

评估出现偏差会出现以下结果：

如果评估过小：

1.原本可以使用并行计划更加有效的，现在使用串行计划

2.不合适的join算法

3.不合适的索引选择，和索引访问方法

如果评估过大：

1.原本使用串行计划更加有效，现在使用并行计划

2.不可合适的join算法

3.不合适的索引选择，和索引访问方法

4.过多的内存分配

5.内存浪费和没必要的并发

内核有以下假设：

Independence：假设，在没有额外的相关信息之外，数据在不同的列是没有关联的

Uniformity：在统计信息的直方图的step，数据分布式均匀分布在step上的。

Containment： 2个表连接，那么高密度的一定被低密度的包含。

Inclusion：如果对一列对常数过滤，那么认为这个常数数据一定存在在这个列中。

当数据库的兼容级别为120的时候，就是启用了新的基数评估，默认使用新的基数评估。

但是可以通过查询跟踪标记来指定：

2312：在兼容级别低于120的时候使用新的基数评估

9481：在兼容级别在120下，使用老的基数评估

可以从图形执行计划或者xml执行计划中找到CardinalityEstimationModelVersion，如果为120就是新的基数评估，70就是老的基数评估。

新的基数评估虽然总体提示了性能，但是对个别查询来说，会被影响，性能变差，所以要测试。

1.在类似生产环境下，测试大多数的负荷

2.可以先迁移到sql server 2014，但是使用不运行在120兼容级别

3.也可以到120兼容级别，但是在全局范围开9481跟踪标记

4.新建数据库推荐使用默认会使用120兼容级别。

没有什么特别的就是通过实际值和评估值对个对比。

评估值偏差，是存在的，那么多少算是偏差太大了？其实没有一个固定的值，主要是看以下2点：

1.偏差是不是造成了资源过度使用

2.偏差是不是造成了特定查询的性能问题

如果任意一个出现问题的话，那么就能认为偏差太大了。

只有评估值变化的情况下，看性能是否下降超过预期，如果超过要进行手动干预。

如果评估值和老CE一样，并且计划没有什么变化，就不需要处理。

1.能够从新基数评估得到性能优化的查询，就使用新基数评估，其他的进行重新调整。

2.有好处的查询使用新的基数评估，其他的使用跟踪标记9481

3.使用老的基数评估，特定的查询可以指定跟踪标记2312

4.直接调试有问题的sql

5.使用老的基数评估

在没有多列统计信息的情况下，SQL Server优化器会认为谓词之间是不相关的。

老的基数评估：各个谓词的选择度相乘

新的基数评估：选择度从低到高排序，然后使用以下公式：

如果超出统计信息范围，那么老的基数评估就认为不存在，评估行数为1。

新的基数评估会用，密度*总行数来当评估。

老的基数评估是以线性增长的方式一步一步对齐2个直方图。(根本不知道是怎么玩的)

新的基数评估，使用相对简单的join评估算法，只是用直方图的最大最小边界来对齐。（文章并没有给出详细的算法很坑爹）。

新的基数评估是用这种原则，很容易发现评估值不够准确。

多个join条件，对于老的基数评估来说，是独立的谓词，是用选择度相乘的方法来组合。

新的基数评估，是用2个不同值个数（distinct value count）中较小的一个，然后乘以2边的平均频率。（搞不懂）

老的基数评估，是独立的谓词，是用选择度相乘的方法来组合。

新的基数评估，认为大表小标多对1的关系。即大表中的一行，必定存在于表的一样与之对应。这个算法把大表的评估作为评估。（这个简单）

如果是等值连接，那么就会假设这个列表2边都是存在的。如果存在join表上有非join谓词，老的基数评估那么会认为一些级别的相关，这种相关叫做简单包含（Simple Containment）。

老的基数评估的JOIN评估，假设在使用join谓词之前，任意存在的谓词会缩小直方图，而谓词之间是不相关的。老CE用这样的评估方式会让评估值偏大。

USE [AdventureWorks2012];

GO

SELECT [od].[SalesOrderID], [od].[SalesOrderDetailID]

FROM Sales.[SalesOrderDetail] AS [od]

INNER JOIN PRoduction.[Product] AS [p]

ON [od].[ProductID] = [p].[ProductID]

WHERE [p].[Color] = 'Red' AND

[od].[ModifiedDate] = '2008-06-29 00:00:00.000'

OPTION (QUERYTRACEON 9481); -- CardinalityEstimationModelVersion 70

新的基数评估是使用基本包含（Base Containment），新的基数评估，是直接从基表上面获取选择度，而不是经过谓词过滤之后。

对于新的基数评估和老的相比在多对多连接中，不同值计数评估相差很小。如果join条件会放大基数，老的基数评估可能会不准确。

新的基数评估根据join谓词和非join谓词选择不同值。新的基数评估使用环境基数（ambient cardinality），环境基数是group by或者distinct列的最小不同值集合（The new CE uses “ambient cardinality”, which is the cardinality of the smallest set of joins that contains the GROUP BY or DISTINCT columns.）。

使用新的xevent，query_optimizer_estimate_cardinality来输出

CREATE EVENT session [CardinalityEstimate] ON SERVER

ADD EVENT sqlserver.query_optimizer_estimate_cardinality

ADD TARGET package0.event_file( SET filename = N'S:/CE/CE_Data.xel' ,