背景: 在统计自动更新设置为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 ModifiedRows FROM 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. 在统计被更新后，更新次数被重置为零
参考链接：http://www.sqlskills.com/blogs/paul/how-are-per-column-modification-counts-tracked/
照例来张图：
