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