SQL Server统计信息

作者：网络转载发布时间：[ 2013/4/10 9:57:43 ] 推荐标签：

发现过期统计信息并处理

　　统计信息是关于谓词中的数据分布的主要信息源，如果不知道具体的数据分布，优化器不能获得预估的数据集，从而不能统计需要返回的数据。

　　在创建列的统计信息后，在DML操作如insert、update、delete后，统计信息会过时。因为这些操作更改了数据，影响了数据分布。此时需要更新统计信息。

　　在高活动的表中，统计信息可能几个小时会过时。对于静态表，可能几个星期才会过时。这要视乎表上DML的操作。

　　从2000开始，SQLServer对增删改操作会增加在表sysindexes中的RowModCtr（Row Modification Counter）值，当统计信息更新后，该值会重置会0，并重新累加。所以查看这个表的这个值可以知道统计信息是否过时。

　　在2000之后，SQLServer改变了这种跟踪方式，把更改存放到对应的数据行上。这个值是未公开的ColModCtr。

　　但是sys.sysindexes到2012依旧可用，还是可以用这个表的数值来确定是否过期。

　　准备工作：

　　本文将用到下面的系统视图和兼容性视图：

　　1、sys.sysindexes：兼容性视图，提供RowModCtr列值，是本文的核心。

　　2、sys.indexes：使用表ID来获得统计信息名。

　　3、sys.objects：获取架构名。

　　步骤：

　　显示RowModCtr值很高的统计信息：

SELECT DISTINCT
        OBJECT_NAME(SI.object_id) AS Table_Name ，
        SI.name AS Statistics_Name ，
        STATS_DATE(SI.object_id， SI.index_id) AS Last_Stat_Update_Date ，
        SSI.rowmodctr AS RowModCTR ，
        SP.rows AS Total_Rows_In_Table ，
        'UPDATE STATISTICS [' + SCHEMA_NAME(SO.schema_id) + '].['
        + OBJECT_NAME(SI.object_id) + ']' + SPACE(2) + SI.name AS Update_Stats_Script
FROM    sys.indexes AS SI( NOLOCK )
        INNER JOIN sys.objects AS SO( NOLOCK ) ON SI.object_id = SO.object_id
        INNER JOIN sys.sysindexes SSI( NOLOCK ) ON SI.object_id = SSI.id
                                                    AND SI.index_id = SSI.indid
        INNER JOIN sys.partitions AS SP ON SI.object_id = SP.object_id
WHERE   SSI.rowmodctr > 0
        AND STATS_DATE(SI.object_id， SI.index_id) IS NOT NULL
        AND SO.type = 'U'
ORDER BY RowModCTR DESC

　　分析：

　　需要了解一些事情：

　　1、从你上次更新统计信息是何时的事情？

　　2、在更新统计信息之后有多少事务发生在表上？

　　3、哪些T-SQL需要用于更新统计信息。

　　4、更新统计信息是否可行？这个是对比RowModCTR列和Total_Rows_In_Table列。

　　当在数据库开启了Auto_Update_Statistics之后，还有数据的话，那有必要更新统计信息。下面有一些规则：

　　1、表大小从0增长。

　　2、当表的数据小于等于500时没有问题，并且ColModCtr从超过500行之后开始增长。

　　3、当表的行数超过500行时，在统计信息对象的引导列的ColModCtr值超过500+20%的行数时，需要更新。

　　例子：有一个100万行的表，优化器会在插入200500行新数据后认为统计信息过时。但是这并不是化的。

　　扩充知识：