当前位置：首页 -> AI编程基础 -> 数据库编程

SQL Server跟踪每一列的修改计数(一)

2014-11-24 16:04:05 · 作者: · 浏览: 1
标签: SQL Server 跟踪 修改 计数
SQL Server跟踪每一列的修改计数
从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 c

下面我将一直用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

第一行是堆的情况，因为我没有建聚集索引。（译者：该行的rowmodctr是完全从SQL Server2000继承而来，表示自上次统计更新以来，对表中行所做的INSERT、DELETE、UPDATE次数。）
下面做一些变化，看看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的sysi