How are per-column modification counts tracked?

Earlier today there was a question on the MVP mailing list asking how SQL Server keeps track of per-column modification counts.

From 2008 onwards, the hidden system table sys.sysrscols tracks modifications to table columns using the rcmodified column. The hidden system tables (introduced in 2005 when we rewrote the entire metadata management system) are only accessible when connected using the Dedicated Administrator Connection (DAC), as I've blogged about many times before. This means connecting using SQLCMD -A or with the prefix 'admin:' on your connection string.

It's also exposed in the sys.system_internals_partition_columns catalog view too, which doesn't require the DAC. (I'd forgotten this – thanks to my friend Remus Rusanu for reminding me!)

Bear in mind, however, that this is inferred behavior based on my background knowledge and observations and may well change completely in future versions – it's all undocumented and so you really shouldn't build anything that relies on this programmatically. If I don't say that then my good friend Conor Cunningham will slap me next time he sees me :-)

Here's an example using a simple table:

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

And we can look at the per-column modification counters using the DAC query:

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

And we get:

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

And you can use the sys.system_internals_partition_columns view as well:

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 

I'll continue with the DAC query against sysrscols directly.

If we do a modification and run the DAC query again:

INSERT INTO t1 VALUES (1, 1, 1);
GO

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

Huh? It didn't show up yet. This is because updates to some system tables are not flushed from the in-memory metadata cache until a checkpoint occurs.

Let's try that, and then run the DAC query again:

CHECKPOINT;
GO

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

And to prove it, I'll update just c2 twice, checkpoint, and run the DAC query again.

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

Cool eh?

What about the sysindexes column rowmodctr? How is that tracked/calculated?

It's calculated as the delta of the sysrscols.rcmodified counter for the leading column in the index since the index column statistics were last rebuilt (or initially created).

Here are some simple indexes on our table, plus a sysindexes query to get the rowmodctr for it:

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

The first row is for the heap, as I didn't created a clustered index. 

Let's make some changes and see how the sysindexes.rowmodctr and sysrscols.rcmodified columns change:

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

Since the nonclustered indexes were created, I performed 3 updates to c1, 2 updates to c2, and 1 update to c3. The sysrscols.rcmodified counter for each of these columns increased by the correct number, but you can see that it isn't tracked for the columns in the nonclustered indexes themselves. Btw, the final column in each of the nonclustered indexes is referring to the hidden physical RID 'column' in the index that points back to the corresponding heap data record.

However, the sysindexes.rowmodctr changed unintuitively. I performed 5 separate modifications to the columns contained in the t1_c1_c2 index and yet its rowmodctr is only set to 3. This is because the algorithm for rowmodctr is to take the per-column delta of sysrscols.rcmodified for the leading index column since the index column statistics were last rebuilt (or initially created).

I'll prove it by updating the statistics, doing 2 modifications to c1, 4 modifications to c2 and checkpointing. We should see the sysrscols.rcmodified for c1 go to 6, for c2 go to 9, and the sysindexes.rowmodctr for t1_c1_c2 changes to 2.

UPDATE STATISTICS t1;
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

Like so. Even though we updated c2 4 times, the sysindexes.rowmodctr for t1_c1_c2 is only set to 2, clearly using the sysrscols.rcmodified delta for c1.

You can play around with these examples to further convince yourself of the current behavior.

Hope this has helped remove some confusion about this. There are unfortunately many, many twists to this and when counts are modified…

Enjoy playing around!