Shulou(Shulou.com)06/01 Report--

How does SQL Server track the change count for each column?

On page 83 of "inside the SQL Server query optimizer", there is a paragraph:

"

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).

"

The following is translated from:

Http://www.sqlskills.com/blogs/paul/how-are-per-column-modification-counts-tracked/

Starting with SQLServer 2008, SQLServer tracks changes to each column of the system table sys.sysrscols through a hidden rcmodified column of the table. Hidden system tables (introduced when SQL Server2005, when we rewrote the entire metadata management system) can only be accessed through a DAC (dedicated administrator connection) connection, which I have described in my previous blog post: you must use a SQLCMD-A connection or add the prefix "admin:" to your connection string.

Column modifications can also be viewed through the sys.system_internals_partition_columns catalog view, which does not require DAC.

Keep in mind, however, that these conclusions are based entirely on my background and observations, and may change completely in future versions-because it is undocumented, you should not create any programs based on the above inferences.

Let's use a simple table as an example:

CREATE TABLE T1 (C1 INT, c2 INT, c3 INT); Go

We use DAC to query the modification count for each column, as shown below:

Select p. [object _ id], p. [index _ id], rs. [rscolid], rs.[ rcmodified] FROM sys.sysrscols rsJOIN sys.partitions pON rs. [rsid] = p.[ partition _ id] WHERE p.[ object _ id] = OBJECT_ID ('t1'); GO

The query results are as follows:

Object_id index_id rscolid rcmodified-----277576027 0 1 0277576027 02 0277576027 0 3 0

Query with sys.system_internals_partition_columns view:

Select p. [object _ id], p. [index _ id], pc. [partition _ column_id], pc. [modified _ count] FROM sys.system_internals_partition_columns pcJOIN sys.partitions pON pc. [partition _ id] = p. [partition _ id] WHERE p.[ object _ id] = OBJECT_ID ('t1'); GO

Now I will always use DAC to query sysrscols directly.

If you make changes to the columns in the table, and then run the DAC query:

INSERT INTO t1VALUES (1,1,1); GOobject_id index_id rscolid rcmodified-----277576027 0 1 0277576027 02 0277576027 0 3 0

Yeah? It hasn't changed! Don't worry, this is because some system tables only brush updates out of memory when a checkpoint (checkpoint) occurs. Let's give it a try and then run the DAC query.

CHECKPOINT;GOobject_id index_id rscolid rcmodified-----277576027 0 1 1277576027 02 1277576027 0 3 1

Below, update c2 only twice, perform the checkpoint, and then run the DAC query.

UPDATE T1 SET c2 = 2 update T1 SET c2 = 3 *

Isn't that cool?

What does the rowmodctr column look like in the Sysindexes view? How does it track the count?

It is the difference in the sysrscols.remodified count of the first column of recorded index statistics since the last statistical reconstruction (or initial creation).

Let's create some simple indexes on the table, and then check the rowmodctr column:

CREATE NONCLUSTERED INDEX t1_c1_c2 ON T1 (C1, c2); CREATE NONCLUSTERED INDEX t1_c3 ON T1 (c3); GOSELECT [name], [rowmodctr] FROM sysindexesWHERE [id] = OBJECT_ID ('T1'); GOname rowmodctr- NULL 3t1_c1_c2 0t1_c3 0

The first line is the heap case, because I didn't build a clustered index. (translator: the sum of any changes that have taken place in the first column of the table since its creation.)

Let's make some changes to see how sysindexes.rowmodctr and sysrscols.rcmodified have changed.

UPDATE T1 SET C1 = 4 0name rowmodctr- update T1 SET C1 = 5 NULL 5t1_c1_c2 3t1_c3 update T1 SET c1 = 6 NULL 5t1_c1_c2 3t1_c3 update T1 SET c2 = 3 0name rowmodctr- T1 SET c3 = 2 0name rowmodctr- 0 4277576027 02 0 0 0 02 0 3 2277576027 2 0277576027 0277576027 0277576027

Because the nonclustered index was created, I updated C1 three times, c2 twice, and c3 once. The sysrscols.rcmodified counters for the corresponding columns are all incremented by the correct values. But you will find that it does not track the columns of the nonclustered index itself. Also, the last column of each nonclustered index is a hidden RID column that points to the data record in the corresponding heap.

However, sysindexes.rowmodctr is not changing as we thought. I made five changes to the columns in the t1_c1_c2 index. However, rowmodctr is only 3. This is because rowmodctr's algorithm tracks changes in sysrscols.rcmodified in the first column of index statistics. So the t1_c1_c2 index just tracks the C1 column. )

To prove it, I updated the statistics, made two changes to C1, made four changes to c2, and then performed a checkpoint. We should find that the sysrscols.rcmodified of C1 is 6, the sysindexes.rowmodctr of c2 is 9, and the sysindexes.rowmodctr of C1 is 2.

UPDATE STATISTICSt1;GOUPDATE T1 SET c1 = 7 0name rowmodctr- update T1 SET c1 = 8 position update T1 SET c2 = 4 NULL 9t1_c1_c2 2t1_c3 update T1 SET c2 = 5 index_id rscolid rcmodified---- T1 update T1 SET c2 = 7 index_id rscolid rcmodified---- 0 0 6277576027 02 9277576027 3 2277576027 2 0277576027 0277576027 0277576027 3 0277576027

That's how it works. Even if we update c2 4 times. The Sysindexes.rowmodctr of t1_c1_c2 is only 2, which is obviously the sysrscols.rcmodified difference of C1.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.