SQL Server如果跟踪每一列的修改计数的?
原文:How areper-column modification counts tracked?
原文地址:http://www.sqlskills.com/blogs/paul/how-are-per-column-modification-counts-tracked/
从SQLServer 2008开始,SQL Server通过一个隐藏的系统表sys.sysrscols的rcmodified列来跟踪表中每列的修改情况。隐藏的系统表(SQL Server2005时引进,当时我们重写了整个元数据管理系统)只有通过DAC(专用管理员连接)连接方式才能存取,我以前的博文有过介绍:必须使用SQLCMD –A连接或者要在你的连接字符串加上前缀“admin:”。
列修改情况也能通过sys.system_internals_partition_columns目录视图查看,这种方式不需要DAC方式。
不过记住,这些完全是基于我的背景知识以及观察而进行推断得出的结论,未来版本中可能会完全改变——因为它是非文档化的,所以你不要基于上面的推断来创建任何程序。
下面用一个简单表举个例子:
CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
GO
我们用DAC查询每一列的修改计数,见下:
SELECT
p.[object_id],
p.[index_id],
rs.[rscolid],
rs.[rcmodified]
FROM sys.sysrscols rs
JOIN sys.partitions p
ON rs.[rsid] = p.[partition_id]
WHERE p.[object_id] = OBJECT_ID ('t1');
GO
查询结果如下:
object_id index_id rscolid rcmodified
———– ——– ———– ———–
277576027 0 1 0
277576027 0 2 0
277576027 0 3 0
用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 ('t1');
GO
下面我将一直用DAC直接查询sysrscols。
如果对表中列做一下修改,然后再运行DAC查询:
INSERT INTO t1VALUES (1, 1, 1);
GO
object_id index_id rscolid rcmodified
———– ———– ———– ——————–
277576027 0 1 0
277576027 0 2 0
277576027 0 3 0
嗯?没有变化嘛!别急,这是因为一些系统表只有在检查点(checkpoint)发生时才会将更新从内存中刷入。我们来试一下,然后再运行DAC查询。
CHECKPOINT;
GO
object_id index_id rscolid rcmodified
———– ———– ———– ——————–
277576027 0 1 1
277576027 0 2 1
277576027 0 3 1
下面仅仅更新c2两次,执行检查点,然后再运行DAC查询。
UPDATE t1 SET c2= 2;
UPDATE t1 SET c2 = 3;
CHECKPOINT;
GO
object_id index_id rscolid rcmodified
———– ———– ———– ——————–
277576027 0 1 1
277576027 0 2 3
277576027 0 3 1
是不是很酷?
Sysindexes视图中的rowmodctr列是什么样子呢?它是如何跟踪计数的呢?
它是记录索引统计的首列自上次统计重建(或初次创建)以来sysrscols.remodified计数的差值。
下面在表上创建一些简单的索引,然后查一下rowmodctr列:
CREATENONCLUSTERED INDEX t1_c1_c2 ON t1 (c1, c2);
CREATE NONCLUSTERED INDEX t1_c3 ON t1 (c3);
GO
SELECT
[name],
[rowmodctr]
FROM sysindexes
WHERE [id] = OBJECT_ID ('t1');
GO
name rowmodctr
—————- ———–
NULL 3
t1_c1_c2 0
t1_c3 0
第一行是堆的情况,因为我没有建聚集索引。(译者:自表创建以来,该表任何统计首列所发生的修改的总和)
下面做一些变化,看看sysindexes.rowmodctr 和 sysrscols.rcmodified 是如何变化的。
UPDATE t1 SET c1= 4;
UPDATE t1 SET c1 = 5;
UPDATE t1 SET c1 = 6;
UPDATE t1 SET c2 = 2;
UPDATE t1 SET c2 = 3;
UPDATE t1 SET c3 = 2;
CHECKPOINT;
GO
object_id index_id rscolid rcmodified
———– ———– ———– ——————–
277576027 0 1 4
277576027 0 2 5
277576027 0 3 2
277576027 2 1 0
277576027 2 2 0
277576027 2 3 0
277576027 3 1 0
277576027 3 2 0
name rowmodctr
—————- ———–
NULL 5
t1_c1_c2 3
t1_c3 1
因为创建了非聚集索引,所以我对c1进行了3次更新,对c2进行了2次更新,对c3进行了一次更新。相应列的sysrscols.rcmodified计数器都增加了正确的值。但是你会发现它并没有跟踪非聚集索引的列本身。还有,每个非聚集索引的最后一列是一个隐藏的RID列,它指向对应堆中的数据记录。
但是,sysindexes.rowmodctr却不是按我们想的变化的。我对t1_c1_c2索引中的列分别做了5次修改。然而rowmodctr却只是3。这是因为rowmodctr的算法是跟踪索引统计的首列的sysrscols.rcmodified的变化值。(所以t1_c1_c2索引只是跟踪c1列。)
为了证明它,我更新统计,对c1做2次修改、对c2做4次修改,然后执行检查点。我们应该发现c1的sysrscols.rcmodified为6,c2的为9;t1_c1_c2的sysindexes.rowmodctr的变为2.
UPDATE STATISTICSt1;
GO
UPDATE t1 SET c1= 7;
UPDATE t1 SET c1 = 8;
UPDATE t1 SET c2 = 4;
UPDATE t1 SET c2 = 5;
UPDATE t1 SET c2 = 6;
UPDATE t1 SET c2 = 7;
CHECKPOINT;
GO
object_id index_id rscolid rcmodified
———– ———– ———– ——————–
277576027 0 1 6
277576027 0 2 9
277576027 0 3 2
277576027 2 1 0
277576027 2 2 0
277576027 2 3 0
277576027 3 1 0
277576027 3 2 0
name rowmodctr
—————- ———–
NULL 9
t1_c1_c2 2
t1_c3 0
就是这样的。即使我们4次更新c2。t1_c1_c2的Sysindexes.rowmodctr也仅仅是2,很明显是c1的sysrscols.rcmodified差值。