背景: 在统计自动更新设置为ON时(默认设置),当更新次数达到一定阀值后便会触发自动更新。

 

本文通过一些测试来探究SQL SERVER 如何追踪数据更新情况和如何计算更新次数。

--==========================================

--可以使用以下系统表来查看列变化情况

--sys.system_internals_partition_columns

--sys.sysindexes

--sys.sysrscols(需要使用DAC)

--==========================================

--生成测试表和索引

DROP TABLE dbo.TB001

GO

CREATE TABLE dbo.TB001

(

   ID INTIDENTITY(1,1) NOT NULL,

   C1 INT,

   C2 INT

)

GO

ALTER TABLE dbo.TB001

ADD CONSTRAINT PK_TB001

PRIMARY KEY(ID)

GO

CREATE INDEX IX_C2 ON dbo.TB001(C1)

--===========================================

--插入10 条数据

INSERT INTO TB001(C1,C2)

SELECT 1,1

GO 10

--============================================

--数据修改不会即时更新到系统表中，需要使用CHECKPOINT

--来将更新flush到系统表中

CHECKPOINT

GO

--============================================

--使用sys.system_internals_partition_columns来

--查看数据的变更(无法获得非聚簇索引上的变化)

SELECT

    p.[object_id],

    p.[index_id],

    pc.[partition_column_id],

    pc.[modified_count]

FROM sys.system_internals_partition_columns pc

JOIN sys.partitions p

    ON pc.[partition_id] = p.[partition_id]

WHERE p.[object_id] = OBJECT_ID('dbo.TB001');

GO

--================================================

--查看非聚簇索引上的变化

SELECT I.name,I.rowcnt,I.rowmodctr

FROM sys.sysindexes I

WHERE I.id=OBJECT_ID('dbo.TB001');

--================================================

--重建索引

ALTER INDEX [PK_TB001] ON [dbo].[TB001] REBUILD

GO

ALTER INDEX [IX_C2] ON [dbo].[TB001] REBUILD

GO

--===============================================

--重建索引后发现被索引的列的值没有发生变化

SELECT

    p.[object_id],

    p.[index_id],

    pc.[partition_column_id],

    pc.[modified_count]

FROM sys.system_internals_partition_columns pc

JOIN sys.partitions p

    ON pc.[partition_id] = p.[partition_id]

WHERE p.[object_id] = OBJECT_ID('dbo.TB001');

GO

 --================================================

--查看非聚簇索引上的变化

--重建索引后，索引变化rowmodctr的值被清零

SELECT I.name,I.rowcnt,I.rowmodctr

FROM sys.sysindexes I

WHERE I.id=OBJECT_ID('dbo.TB001');

GO

--================================

--对一行数据更新110 遍

UPDATE TB001

SET C1=2

WHERE ID=1

GO 110

CHECKPOINT

--================================================

--查看非聚簇索引上的变化

--发现单行数据被修改110 次，rowmodctr被置为 110

--即使值未发生变化，也被记录为一次修改

 

SELECT I.name,I.rowcnt,I.rowmodctr

FROM sys.sysindexes I

WHERE I.id=OBJECT_ID('dbo.TB001');

GO

--==============================

--更新统计

sp_updatestats

--================================================

--查看非聚簇索引上的变化

--统计被更新后，rowmodctr被重置为0

SELECT I.name,I.rowcnt,I.rowmodctr

FROM sys.sysindexes I

WHERE I.id=OBJECT_ID('dbo.TB001');

GO

 

使用脚本查看

--查看最后统计更新的时间及最后一次更新后数据更新的次数SELECT  T.[name] AS TableName ,        S.[name] AS StatsName ,        P.last_updated AS LastUpdatedTime ,        P.[rows] AS TableRows ,        P.rows_sampled AS SampledRows ,        P.modification_counter AS ModifiedRowsFROM    sys.tables T        INNER JOIN sys.stats S ON T.[object_id] = S.[object_id]        OUTER APPLY sys.dm_db_stats_properties(S.[object_id], s.[stats_id]) AS P--WHERE T.[name]=''

 

总结：

1. 使用sys.system_internals_partition_columns来查看聚簇索引上的更新次数

2. 使用sys.sysindexes来查看非聚簇索引上的更新次数

3. 重建索引会导致更新次数数据被重置为零（建议在索引重建后更新统计）

4. 更新次数计算的是更新操作的次数（即使值未发生变化，也被记录为一次修改）

5. 在统计被更新后，更新次数被重置为零

 

参考链接：

 

照例来张图：