正文

SQL Server研究之统计信息—发现过期统计信息并处理具体解释

IP云
IP云V管理员/6 阅读
08/08
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示


前言：

统计信息是关于谓词中的数据分布的主要信息源，假设不知道详细的数据分布，优化器不能获得预估的数据集。从而不能统计须要返回的数据。

在创建列的统计信息后，在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及以后版本号才可用。