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

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

《inside the SQL Server query optimizer》第83页，有这样一段话：

“

SQL Server defnes when statistics are out of date by using column modifcation

counters or colmodctrs, which count the number of table modifcations, and which are

kept for each table column. Basically, for tables bigger than 500 rows, a statistics object

is considered out of date if the colmodctr value of the leading column has changed by

more than 500 plus 20% of the number of rows in the table. The same formula is used

by fltered statistics but, since they are built only from a subset of the records of the

table, the colmodctr value is frst adjusted depending on the selectivity of the flter.

Colmodctrs are usually not exposed by any SQL Server metadata although they can be

accessed by using a dedicated administrator connection and looking at the rcmodified

column of the sys.sysrscols base system table in SQL Server 2008 (same information

can be found on the sysrowset columns for SQL Server 2005).

”

下文翻译自：

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列：

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