SQL Server研究之统计信息—发现过期统计信息并处理详解,sql统计信息
前言:
统计信息是关于谓词中的数据分布的主要信息源,如果不知道具体的数据分布,优化器不能获得预估的数据集,从而不能统计需要返回的数据。
在创建列的统计信息后,在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值很高的统计信息:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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行新数据后认为统计信息过时。但是这并不是绝对化的。
扩充知识:
没有直接的方式访问ColModCtr的值,因为它只是用于优化引起,并且对用户透明,但是可以使用DAC(专用管理员连接)来访问sys.sysrscols.rcmodified系统。但是仅在2008R2及以后版本才可用。
删除链接:
当数据库有一个或多个活动连接时,则“状态”为“未就绪”,“消息”列将显示“<活动连接数> 活动连接” - 例如:“1 活动连接”。在分离数据库之前,需要通过选择“删除连接”断开所有活动连接。
更新统计信息:
默认情况下,分离操作将在分离数据库时保留过期的优化统计信息;若要更新现有的优化统计信息,请选中“更新统计信息”复选框。
SELECT count(*)
FROM sysobjects
WHERE (xtype = 'U')
sysobjects是sql server 的系统表,用于记录数据库中的各种对象。
你可以在企业管理器下,右击数据库服务器在弹出的菜单中选“编辑sql server注册属性”,然后在“显示系统数据库和系统对象”前打上对勾。
然后你在打开你在查询的那个数据库,就会看到隐藏的系统表,其中就有一个sysobjects表.