Sql Server优化---统计信息维护策略

　　如果统计信息取样百分比过低，会影响到统计信息的准确性，
　　如果过于暴力，比如fullscan的方式扫描，
　　参考下图，一个表就Update了50分钟(当然这是一个大表，上面有多个索引统计信息以及非索引统计信息)。如果有数十张类似的表，效率可想而知
　　总之就是，没有一个固定的方式，数据库不大，怎么做问题都不大，数据库一大，加上维护的窗口期时间有限，要在统计信息的质量和维护效率上综合考虑

　　

 

3，数据库级别的sp_updatestats

　　用法：
　　exec sp_updatestats
　　或者
　　exec sp_updatestats @resample = ‘resample‘

　　指定 sp_updatestats 使用 UPDATE STATISTICS 语句的 RESAMPLE 选项。

　　对于基于默认抽样的查询计划并非最佳的特殊情况，SAMPLE 非常有用。
　　在大多数情况下，不必指定 SAMPLE，
　　这是因为在默认情况下，查询优化器根据需要采用抽样，并以统计方式确定大量样本的大小，以便创建高质量的查询计划。

　　如果未指定 ‘resample‘，则 sp_updatestats 将使用默认的抽样来更新统计信息。 
　　默认值为 NO。

　　直接执行exec sp_updatestats更新统计信息，取样密度是默认的，
　　究竟这默认值是多少，MSDN上说默认情况下是“查询优化器根据需要采用抽样”,我想着采样算法应该没那么简单粗暴
　　目前也不知道具体是怎么一个算法或者采样方式，如果有知道园友的话请不惜赐教，谢谢

 

4，TraceFlag 2371

开启TraceFlag 2371之后，统计信息的变化是根据表做动态变化的，
打破了触发大表统计信息更新的当表中行多于500行时，数据的变化量大于500+20%*表中数据行数 阈值
参考：https://blogs.msdn.microsoft.com/saponsqlserver/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371/

　　在下图中，你可以看到新公式的工作方式，对于小表，阈值仍旧是在20%左右，
　　只有超过25000行之后，此动态规则才会被触发生效
　　随着表中数据行数的增加，(触发统计信息变更)的百分比会变的越来越低，
　　比如，对于100,00行的表，触发统计信息更新的阈值已经降低为10%，
　　对于1,000,000行的表，触发统计信息更新的阈值已经降低为3.2%。

　　

　　对于10,000,000或者是50,000,000行的表，触发统计信息更新的阈值为少于1%或者0.5%，
　　而对于他100,000,000行的表，仅仅要求变化在0.31%左右，就可以出发统计信息的更新。

　　但是个人认为，这种方式也不一定靠谱，虽然开启TraceFlag 2371之后触发更新索引统计信息的阈值降低了，但是取样百分比还是一个问题，
　　之前我自己就有一个误区，看统计信息的时候只关注统计信息的更新时间（跟自己之前遇到的数据库或者表太小有关）
　　对于统计信息，及时更新了（更新时间比较新）不等于这个统计信息是准确的，一定要看取样的行数所占总行数的百分比

 

如何有效维护索引统计信息？

　　上面说了，要使获取相对准确的统计信息，就要在更新统计信息时候的取样百分比，
　　对于小表，即便按照其默认的变化阈值触发统计信息更新，或者是按照100%取样更新统计信息，都是没有问题，
　　对于大表，一定要考虑在其达到默认触发统计信息更新的阈值之前人为更新这个统计信息，但是大表的100%取样统计是不太现实的（性能考虑）
　　取样百分比越高，得到的统计信息越准确，但是代价越大，这就需要找一个平衡点，那么如果更新大表上的统计信息呢？
　　如果是认为干预统计信息的生成，就要考虑两个因素：一是数据变化了多少之后更新？二是更新的时候，以什么样的取样来更新？
　　我们知道，一个表的数据变化信息（增删改）记录在sys.sysindexes这个系统表的rowmodctr字段中，
　　该表的统计信息更新之后，该字段清零，然后再次累积记录表上的数据变化。

　　

　　这个信息非常好使，为人工更新统计信息提供了重要的依据，
　　比如，对于1000W行的表，可以指定变化超过20W行（根据业务情况自定义）之后，手动更新统计信息，
　　对于5000W行的表，可以指定变化超过60W行（根据业务情况自定义）之后，手动更新统计信息，
　　同时根据不同的表，在相对较小的表上，指定相对较高的取样百分比，在相对较大的表上，指定相对较低的取样百分比
　　比如对于1000W行的表，更新统计信息的时候取样百分比定位60%，对于5000W行的表，更新统计信息的时候取样百分比定位30%
　　这样，可以自行决定数据变化了多少之后更新统计信息，以及动态地决定不同表的不同取样百分比，达到一个合理的目的。
　　当然，最后强调一下，我说的每一个数据都是相对的，而不是绝对的，都是仅做参考，
　　具体还要你自己结合自己的服务器软硬件以环境及维护窗口时间去尝试，一切没有死的标准。

 

总结：统计信息的准确性对执行计划的生成有着至关重要的影响，本文粗略分析了统计信息的跟新规律以及要更新统计信息时候要注意的问题，
　　 在人为干预统计信息更新的时候，需要根据具体的情况（表数据流量，服务器软硬件环境，维护窗口期等）在效率与准确性之间作出合理的选择。

Sql Server优化---统计信息维护策略

标签：记录   级别   综合   获取数据   信息   tps   目的   人生   数据