SQL Server统计信息更新时采样百分比对数据预估准确性的影响有哪些

共计 4119 个字符，预计需要花费 11 分钟才能阅读完成。

行业资讯    
数据库    
SQL Server 统计信息更新时采样百分比对数据预估准确性的影响有哪些

SQL Server 统计信息更新时采样百分比对数据预估准确性的影响有哪些，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

为什么要写统计信息

最近看到园子里有人写统计信息，楼主也来凑热闹。

话说经常做数据库的，尤其是做开发的或者优化的，统计信息造成的性能问题应该说是司空见惯。

当然解决办法也并非一成不变，“一招鲜吃遍天”的做法已经行不通了（题外话：整个时代不都是这样子吗）

当然，还是那句话，既然写了就不能太俗套，写点不一样的，通过分析一个类似实际案例来解读统计信息的更新的相关问题。

对于实际问题，不但要解决问题，更重要的是要从理论上深入分析，才能更好地驾驭数据库。

何时更新统计信息

（1）查询执行缓慢，或者查询语句突然执行缓慢。这种场景很可能是由于统计信息没有及时更新而遭遇了参数嗅探的问题。

（2）当大量数据更新（INSERT/DELETE/UPDATE）到升序或者降序的列时，这种情况下，统计信息直方图可能没有及时更新。

（3）建议在除索引维护（当你重建、整理碎片或者重组索引时，数据分布不会改变）外的维护工作之后更新统计信息。

（4）数据库的数据更改频繁，建议最低限度每天更新一次统计信息。数据仓库可以适当降低更新统计信息的频率。

（5）当执行计划出现统计信息缺失警告时，需要手动建立统计信息

统计信息基础

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

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

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

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

做个查询，触发统计信息更新，rowmodct 归 0（继续累积直到下一个触发的阈值，触发更新之后再次归 0）

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

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

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

这一点对于大表来说通常影响是比较大的，比如 1000W 的表，变化超过 20% 也 +500 也就是 200W+500 行之后才触发统计信息更新，这个阈值区间的自动触发阈值，绝大多数情况是不能接受的，于是对于统计信息的诊断就变成了是否“过期”

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

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

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

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

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

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

下面开始本文的主题：

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

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

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

CREATE TABLE [dbo].[TestStaitisticsSample]( [Id] [int] IDENTITY(1,1) NOT NULL, [ParentId] [int] NULL, [OtherColumn] [varchar](50) NULL) declare @i int=0while(@i 100000000)begin insert into [TestStaitisticsSample](ParentId,OtherColumn)values(@i,NEWID()) /*  中间插入 50 条，也即一个主表 Id 对应 50 条子表明细  */ insert into [TestStaitisticsSample](ParentId,OtherColumn)values(@i,NEWID()) set @i=@i+1endgocreate nonclustered index [idx_ParentId] ON [dbo].[TestStaitisticsSample]( [ParentId] )go

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

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

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

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

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

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

1，对于取样的 RANG_HI_Key 值，比如 51632，预估了 862.212 行

2，对于 AVG_RANG_ROW，比如 45189 到 51632 之间的每个 Id 的数据对应的数据行，预估是 6682.490 行

之前造数据的时候每个 Id 都是 50 行，这里的预估靠谱吗，这个误差是无法接受的，

很多时候，对于大表，采用默认（未指定采样密度）的情况下，默认的采样密度并不足以准确地描述数据分布情况

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

这一次用 20% 的采样密度，可以看到取样的行数是 15898626 行

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

2，对于 AVG_RANG_ROW，比如 186302 到 216305 之间的每个 Id 的行数，预估是 197.4439 行

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

整体上看，但是这个误差还是比较大的，如果继续提高采样密度，看看有什么变化？

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

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

1，对于取样的 RANG_HI_Key 值，比如 1978668，预估了 71.15906 行

2，对于 AVG_RANG_ROW，比如 1124024 到 1978668 之间的每个 Id，预估为 61.89334 行

可以说，对于绝大多数值得预估（AVG_RANG_ROW），都愈发接近于真实值

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

可以看到，取样行数等于总行数，也就是所谓的全部（100%）取样

看一下预估结果：

比如 Id=3981622，预估是 50 行，3981622 与 4131988 之间的 Id 的行数，预估为 49.99874 行，基本上等于真实数据分布

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

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

至于为什么默认取样密度和较低取样密度情况下，误差很大的情况我简单解释一下，也非常容易理解，因为“子表”中存储主表 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 服务，瞬间内存释放了 7 个 G，可见这些个操作还是比较耗内存的

通过对于某些场景下，在对较大的表的索引统计信息更新时，采样密度的分析，阐述了不同采样密度下，对统计信息预估的准确性的影响。

当然对于小表，一些都好说。

随着单表数据量的增加，统计信息的更新策略也要做相应的调整，不光要看统计信息是否“过期”，更重要的是注意统计信息更新时究竟取样了全表的多少行数据做统计。

对于大表，采用 FULL SCAN 或者 100% 采样往往是不可行的，这时候就需要做出权衡，做到既能准确地预估，又能够以合理的代价执行。

看完上述内容，你们掌握 SQL Server 统计信息更新时采样百分比对数据预估准确性的影响有哪些的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！