SQL Server是如何跟蹤每一列的修改計數(shù)的？

SQL Server是如何跟蹤每一列的修改計數(shù)的？

《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通過一個隱藏的系統(tǒng)表sys.sysrscols的rcmodified列來跟蹤表中每列的修改情況。隱藏的系統(tǒng)表（SQL Server2005時引進(jìn)，當(dāng)時我們重寫了整個元數(shù)據(jù)管理系統(tǒng)）只有通過DAC（專用管理員連接）連接方式才能存取，我以前的博文有過介紹：必須使用SQLCMD –A連接或者要在你的連接字符串加上前綴“admin:”。

列修改情況也能通過sys.system_internals_partition_columns目錄視圖查看，這種方式不需要DAC方式。

不過記住，這些完全是基于我的背景知識以及觀察而進(jìn)行推斷得出的結(jié)論，未來版本中可能會完全改變——因為它是非文檔化的，所以你不要基于上面的推斷來創(chuàng)建任何程序。

下面用一個簡單表舉個例子：

CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
Go

我們用DAC查詢每一列的修改計數(shù)，見下：

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

查詢結(jié)果如下：

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

嗯？沒有變化嘛！別急，這是因為一些系統(tǒng)表只有在檢查點（checkpoint）發(fā)生時才會將更新從內(nèi)存中刷入。我們來試一下，然后再運行DAC查詢。

CHECKPOINT;
GO

object_id index_id rscolid rcmodified
———– ———– ———– ——————–
277576027 0 1 1
277576027 0 2 1
277576027 0 3 1

下面僅僅更新c2兩次，執(zhí)行檢查點，然后再運行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列是什么樣子呢？它是如何跟蹤計數(shù)的呢？

它是記錄索引統(tǒng)計的首列自上次統(tǒng)計重建（或初次創(chuàng)建）以來sysrscols.remodified計數(shù)的差值。

下面在表上創(chuàng)建一些簡單的索引，然后查一下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

第一行是堆的情況，因為我沒有建聚集索引。（譯者：自表創(chuàng)建以來，該表任何統(tǒng)計首列所發(fā)生的修改的總和）

下面做一些變化，看看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

因為創(chuàng)建了非聚集索引，所以我對c1進(jìn)行了3次更新，對c2進(jìn)行了2次更新，對c3進(jìn)行了一次更新。相應(yīng)列的sysrscols.rcmodified計數(shù)器都增加了正確的值。但是你會發(fā)現(xiàn)它并沒有跟蹤非聚集索引的列本身。還有，每個非聚集索引的最后一列是一個隱藏的RID列，它指向?qū)?yīng)堆中的數(shù)據(jù)記錄。

但是，sysindexes.rowmodctr卻不是按我們想的變化的。我對t1_c1_c2索引中的列分別做了5次修改。然而rowmodctr卻只是3。這是因為rowmodctr的算法是跟蹤索引統(tǒng)計的首列的sysrscols.rcmodified的變化值。（所以t1_c1_c2索引只是跟蹤c1列。）

為了證明它，我更新統(tǒng)計，對c1做2次修改、對c2做4次修改，然后執(zhí)行檢查點。我們應(yīng)該發(fā)現(xiàn)c1的sysrscols.rcmodified為6，c2的為9；t1_c1_c2的sysindexes.rowmodctr的變?yōu)?.

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