统计信息基础

首先说一个老掉牙的话题,统计信息的更新阈值:

1,表格从没有数据变成有大于等于1条数据。

2,对于数据量小于500行的表格,当统计信息的第一个字段数据累计变化量大于500以后。

3,对于数据量大于500行的表格,当统计信息的第一个字段数据累计变化量大于500 + (20%×表格数据总量)以后。


SQLServer统计信息更新时采样百分比对数据预估准确性的影响

触发统计信息后,rowmodct归0


SQLServer统计信息更新时采样百分比对数据预估准确性的影响

关于统计信息“过期”的问题

下面开始正文,网络上很多关于统计信息的文章,提到统计信息,很多都是统计信息过期的问题,然后跟新之后怎么怎么样

尤其在触发统计信息自动更新阈值的第三个区间:也就是说数据累计变化超过20%之后才能自动触发统计信息的更新

这一点对于大表来说通常影响是比较大的,比如1000W的表,变化超过20%也+500也就是200W+500行之后才触发统计信息更新,

这个阈值区间的自动触发阈值,绝大多数情况是不能接受的,于是对于统计信息的诊断就变成了是否“过期”


SQLServer统计信息更新时采样百分比对数据预估准确性的影响

判断统计信息是否过期,然后通过更新统计信息来促使执行计划更加准确地预估行数,这一点本无可厚非

但是,问题也就出在这里了:那么怎么更新统计信息?一成不变的做法是否可行,这才是问题的重点。

当然肯定有人说,我就是按照默认方式更新的,更新完之后SQL也变得更加优化了什么的

通过update statistics TableName StatisticName更新某一个索引的统计信息,

或者update statistics TableName更新全表的统计信息

这种情况下往往是小表上可以这么做,当然对于大表或者小表没有一个标准值,一切要结合事实来说明问题

下面开始本文的主题:

抽象并简化出业务中的一个实际案例,创建这么一张表,类似于订单和订单明细表(主子表),

这里你可以想象成是一个订单表的子表,Id字段是唯一的,有一个ParentID字段,是非唯一的,

ParentID类似于主表的Id,测试数据按照一个主表Id对应50条子表明细的规律插入数据


SQLServer统计信息更新时采样百分比对数据预估准确性的影响
CREATE TABLE [dbo].[TestStaitisticsSample](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ParentId] [int] NULL,
[OtherColumn] [varchar](50) NULL

)

declare @i int=0

while(@i<100000000)

begin

insert into [TestStaitisticsSample](ParentId,OtherColumn)values(@i,NEWID())

/*

中间插入50条,也即一个主表Id对应50条子表明细

*/

insert into [TestStaitisticsSample](ParentId,OtherColumn)values(@i,NEWID())

set @[email protected]+1

end

go

create nonclustered index [idx_ParentId] ON [dbo].[TestStaitisticsSample]

(

[ParentId]

)

go


SQLServer统计信息更新时采样百分比对数据预估准确性的影响

本来打算插入1亿条的,中间我让他执行我睡午觉去了,醒来之后发现SSMS挂掉了,挂掉了算了,数据也接近1亿了,能说明问题就够了

现在数据分布的非常明确,就是一个ParentId有50条数据,这一点首先要澄清。

测试数据写入,以及所创建完成之后来更新 idx_ParentId 索引上的统计信息,就按照默认的方式来更新,然后来观察统计信息

默认方式更新统计信息(未指定采样密度)

表里现在是8000W多一点记录,默认更新取样时462239行,那么这个统计信息靠谱吗?

上面说了,造数据的时候,我一个ParentId对应的是50行记录,这一点非常明确,他这里给我统计出来的多少?

1,对于取样的RANG_HI_Key值,比如51632,他给我预估了862.212行

2,对于AVG_RANG_ROW,比如45189到51632之间,他给我预估了6682.490行

这靠谱吗,这个误差是无法接受的,很多时候,对于大表,采用默认(未指定采样密度)的情况下,默认的采样密度并不足以准确地描述数据分布情况


SQLServer统计信息更新时采样百分比对数据预估准确性的影响

指定一个采样密度的方式更新统计信息(20%采样)

这一次用20%的采样密度,可以看到取样时15898626行

1,对于取样的RANG_HI_Key值,比如216305,他给我预估了24.9295行

2,对于AVG_RANG_ROW,比如186302到216305之间,他给我预估了197.4439行

观察比如上面默认的取样密度,这一次不管是RANG_HI_Key还是AVG_RANG_ROW得预估,都有不一个非常高的下降,趋于接近于真实的数据分布(50行)

但是这个误差还是比较大的,如果继续提高采样密度,看看有什么变化?


SQLServer统计信息更新时采样百分比对数据预估准确性的影响

指定一个采样密度的方式更新统计信息(70%采样)

这一次用70%的采样密度,可以看到取样是55962290行

1,对于取样的RANG_HI_Key值,比如1978668,他给我预估了71.15906行

2,对于AVG_RANG_ROW,比如1124024到1978668之间,他给我预估了61.89334行

可以说,对于绝大多数值得预估(AVG_RANG_ROW),都愈发接近于真实值


SQLServer统计信息更新时采样百分比对数据预估准确性的影响

指定一个采样密度的方式更新统计信息(100%采样)

这个就不做过多解释了,基本上跟真实值是一样的,只是AVG_RANG_ROW有一点非常非常小的误差。


SQLServer统计信息更新时采样百分比对数据预估准确性的影响

取样密度高低与统计信息准确性的关系

至于为什么默认取样密度和较低取样密度情况下,误差很大的情况我简单解释一下,也非常容易理解,

因为“子表”中存储主表ID的ParentId值允许重复,在存在重复值的情况下,如果采样密度不够,极有可能造成“以偏概全”的情况

比如对10W行数据取样1W行,原本10W行数剧中有2000个不重复的ParentId值,

如果是10%的取样,在1W行取样数据中,因为密度不够大,只找到了20个不重复的ParentId值,

那么就会认为每一行ParentId对应500行数据,这根实际的分布的每个ParentId有一个非常大的误差范围

如果提高采样密度,那么这个误差就会越来越小。

因此在观察统计信息是否过期,决定更新统计信息的时候,一定要注意取样的密度,

就是说表中有多少行数据,统计信息更新的时候取了多少采样行,密度有多高。

当然,肯定有人质疑,那你说采样密度越高,也就是取样行数越高越准确,那么我就100%取样。

这样行不行?

还要分情况看,对于几百万或者十几万的小表来说,当然没有问题,这也是为什么数据库越小,表数据越少越容易掩盖问题的原因。

对于大表,上亿的,甚至是十几亿的,你按照100%采样试一试?

举个实际例子:

我这里对一个稍微大一点的表做个全表统计信息的更新,测试环境,服务器没负载,存储是比普通的机械硬盘要强很多的SAN存储

采用full scan,也就是100%采样的更新操作,看一下,仅仅这一样表的update statistic操作就花费了51分钟

试想一下,对一个数百GB甚至数TB的库来说,你敢这么搞一下。


SQLServer统计信息更新时采样百分比对数据预估准确性的影响

扯一句,这个中秋节过的,折腾了大半天,话说做测试过程中电脑有开始有点卡,

做完测试之后停掉SQLServer服务,瞬间内存释放了7个G,可见这些个操作还是比较耗内存的


SQLServer统计信息更新时采样百分比对数据预估准确性的影响

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQL数据硬盘服务器常大TI数据库
分页:12
转载请注明
本文标题:SQLServer统计信息更新时采样百分比对数据预估准确性的影响
本站链接:http://www.codesec.net/view/484665.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(34)