澳门新蒲京娱乐

图片 1
明天看了少数链表,线性表的链式存款和储蓄
图片 1
驱动建立的链接服务器调用存储过程时参数不能为NULL值

基数评估,2014新特性基数评估引起一些性能问题

    在前阵子写的一篇博文“SQL SERVER 2014 下IF EXITS
居然引起执行计划变更的案例分享”里介绍了数据库从SQL SERVER 2005升级到 SQL
SERVER
2014后,发现一个SQL出现性能问题,当时分析后发现执行计划变了,导致SQL出现了性能问题。但是没有彻底搞清楚为什么出现这种情况。当时看到Actual
Number of Rows 与Estimated Number of
Rows之间的偏差较大(统计信息是最新的),以为是优化器的Bug造成的。其实罪魁祸首是SQL
SERVER 2014新特性——基数评估(Cardinality Estimator)所引起的。IF
EXISTS完全成了我这个标题党的替罪羊(罪过罪过)。下面我再就这个问题展开做一次分析。

基数评估

目录

基数评估…
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对基数评估做了修改。

   
查看该SQL语句的实际执行计划,在属性里面我们可以看到CardinalityEstimationModelVersion的值为120,120表示这是新的基数评估,70就是老的基数评估

基数评估准确的重要性

基数评估提供以下信息:

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

 

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

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

如果评估过小:

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

2.不合适的join算法

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

如果评估过大:

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

2.不可合适的join算法

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

4.过多的内存分配

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

 

图片 2

模型假设

内核有以下假设:

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

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

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

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

其实当数据库的兼容级别为120的时候,默认使用新的基数评估。也就是说启用了新的基数评估,那么我们现在使用查询跟踪标记9481来关闭新的基数评估,使用老的基数评估。

启用新的基数评估

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

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

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

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

DBCC TRACEON(9481, 1);

 

GO

验证基数评估的版本

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

图片 3

图片 4

启用跟踪标记9481后,这个SQL语句的执行计划变了(可以对比图4),可以看到CardinalityEstimationModelVersion的值也变为了70。SQL语句一秒就执行完了。这个是因为基数评估出现了偏差导致了不合适的JOIN算法。

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

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

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

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

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

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

图片 5

校验基数评估

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

图片 6

偏差问题

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

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

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

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

我们对比下面”图四:旧执行计划“,发现其实还是使用Nested
Loops,只是外部循环表与内部循环表变了。   

需要手动处理的变化

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

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

图四:旧执行计划

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

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

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

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

4.直接调试有问题的sql

5.使用老的基数评估

图片 7

SQL Server 2014中的修改

那么关于新的基数评估(Cardinality
Estimator)特性,你想多了解一些这方面的知识,可以参考官方文档Optimizing
Your Query Plans with the SQL Server 2014 Cardinality Estimator。
中文翻译版本可以参考SQL Server
2014新特性——基数评估(白皮书阅读笔记)。下面是官方文档关于基数评估出现偏差可能会造成的一些后果:

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

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

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

新的基数评估:选择度从低到高排序,然后使用以下公式:图片 8

 

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

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

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

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

Join评估算法修改

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

简单Join

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

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

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

如果评估过小:

多Join条件

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

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

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

Join带相等和不相等的谓词

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

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

2.不合适的join算法

Join包含(Containment)假设的修改

如果是等值连接,那么就会假设这个列表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

 

图片 9

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

图片 10

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

不同值计数评估的变化

对于新的基数评估和老的相比在多对多连接中,不同值计数评估相差很小。如果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’ ,

                                  max_rollover_files
=( 2
) )

WITH (
MAX_MEMORY = 4096 KB ,

        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,

        MAX_DISPATCH_LATENCY = 30 SECONDS ,

        STARTUP_STATE =
OFF );

GO

 

— Start the session

ALTER EVENT SESSION [CardinalityEstimate] ON SERVER
STATE=START;

 

— Your workload to be analyzed executed here (or in
another session)

 

— Stop the session after the workload is executed

ALTER EVENT SESSION [CardinalityEstimate] ON SERVER
STATE=STOP;

也可以使用A first look
at the query_optimizer_estimate_cardinality XE
event中的方法。

相关文章

No Comments, Be The First!
近期评论
    功能
    网站地图xml地图