SQL Server如果跟踪每一列的修改计数的？

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差值。