{"id":507,"date":"2012-03-13T11:30:00","date_gmt":"2012-03-13T11:30:00","guid":{"rendered":"\/blogs\/paul\/post\/How-are-per-column-modification-counts-tracked.aspx"},"modified":"2017-04-13T09:50:47","modified_gmt":"2017-04-13T16:50:47","slug":"how-are-per-column-modification-counts-tracked","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/","title":{"rendered":"How are per-column modification counts tracked?"},"content":{"rendered":"

\nEarlier today there was a question on the MVP mailing list asking how SQL Server keeps track of per-column modification counts.<\/font>\n<\/p>\n

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

\nIt's also exposed in the sys.system_internals_partition_columns<\/font> catalog view too, which doesn't require the DAC. (I'd forgotten this – thanks to my friend Remus Rusanu for reminding me!)<\/font>\n<\/p>\n

\nBear 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 :-)<\/font>\n<\/p>\n

\nHere's an example using a simple table:<\/font>\n<\/p>\n

\n

\n\tCREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
\n\tGO<\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd we can look at the per-column modification counters using the DAC query:<\/font>\n<\/p>\n

\n

\n\tSELECT
\n\t    p.[object_id],
\n\t    p.[index_id],
\n\t    rs.[rscolid],
\n\t    rs.[rcmodified]
\n\tFROM sys.sysrscols rs
\n\tJOIN sys.partitions p
\n\t    ON rs.[rsid] = p.[partition_id]
\n\tWHERE p.[object_id] = OBJECT_ID ('t1');
\n\tGO<\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd we get:<\/font>\n<\/p>\n

\n

\n\tobject_id   index_id    rscolid     rcmodified
\n\t———– ———– ———– ———–
\n\t277576027   0           1           0
\n\t277576027   0           2           0
\n\t277576027   0           3           0<\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd you can use the sys.system_internals_partition_columns<\/font> view as well:<\/font>\n<\/p>\n

\n

\n\tSELECT
\n\t    p.[object_id],
\n\t    p.[index_id],
\n\t    pc.[partition_column_id],
\n\t    pc.[modified_count]
\n\tFROM sys.system_internals_partition_columns pc
\n\tJOIN sys.partitions p
\n\t    ON pc.[partition_id] = p.[partition_id]
\n\tWHERE p.[object_id] = OBJECT_ID ('t1');
\n\tGO <\/font>\n\t<\/p>\n<\/blockquote>\n

\nI'll continue with the DAC query against sysrscols<\/font> directly.<\/font>\n<\/p>\n

\nIf we do a modification and run the DAC query again:<\/font>\n<\/p>\n

\n

\n\tINSERT INTO t1 VALUES (1, 1, 1);
\n\tGO<\/font>\n\t<\/p>\n

\n\tobject_id   index_id    rscolid     rcmodified
\n\t———– ———– ———– ——————–
\n\t277576027   0           1           0
\n\t277576027   0           2           0
\n\t277576027   0           3           0<\/font>\n\t<\/p>\n<\/blockquote>\n

\nHuh? 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.<\/font>\n<\/p>\n

\nLet's try that, and then run the DAC query again:<\/font>\n<\/p>\n

\n

\n\tCHECKPOINT;
\n\tGO<\/font>\n\t<\/p>\n

\n\tobject_id   index_id    rscolid     rcmodified
\n\t———– ———– ———– ——————–
\n\t277576027   0           1           1
\n\t277576027   0           2           1
\n\t277576027   0           3           1<\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd to prove it, I'll update just c2<\/font> twice, checkpoint, and run the DAC query again.<\/font>\n<\/p>\n

\n

\n\tUPDATE t1 SET c2 = 2;
\n\tUPDATE t1 SET c2 = 3;
\n\tCHECKPOINT;
\n\tGO<\/font>\n\t<\/p>\n

\n\tobject_id   index_id    rscolid     rcmodified
\n\t———– ———– ———– ——————–
\n\t277576027   0           1           1
\n\t277576027   0           2           3
\n\t277576027   0           3           1<\/font>\n\t<\/p>\n<\/blockquote>\n

\nCool eh?<\/font>\n<\/p>\n

\nWhat about the sysindexes<\/font> column rowmodctr<\/font>? How is that tracked\/calculated?<\/font>\n<\/p>\n

\nIt's calculated as the delta of the sysrscols.rcmodified<\/font> counter for the leading column in the index since the index column statistics were last rebuilt (or initially created).<\/font>\n<\/p>\n

\nHere are some simple indexes on our table, plus a sysindexes<\/font> query to get the rowmodctr<\/font> for it:<\/font>\n<\/p>\n

\n

\n\tCREATE NONCLUSTERED INDEX t1_c1_c2 ON t1 (c1, c2);
\n\tCREATE NONCLUSTERED INDEX t1_c3 ON t1 (c3);
\n\tGO<\/font>\n\t<\/p>\n

\n\tSELECT
\n\t    [name],
\n\t    [rowmodctr]
\n\tFROM sysindexes
\n\tWHERE [id] = OBJECT_ID ('t1');
\n\tGO<\/font>\n\t<\/p>\n

\n\tname             rowmodctr
\n\t—————- ———–
\n\tNULL             3
\n\tt1_c1_c2         0
\n\tt1_c3            0
\n\t<\/font>\n\t<\/p>\n<\/blockquote>\n

\nThe first row is for the heap, as I didn't created a clustered index. <\/font>\n<\/p>\n

\nLet's make some changes and see how the sysindexes.rowmodctr<\/font> and sysrscols.rcmodified<\/font> columns change:<\/font>\n<\/p>\n

\n

\n\tUPDATE t1 SET c1 = 4;
\n\tUPDATE t1 SET c1 = 5;
\n\tUPDATE t1 SET c1 = 6;
\n\tUPDATE t1 SET c2 = 2;
\n\tUPDATE t1 SET c2 = 3;
\n\tUPDATE t1 SET c3 = 2;
\n\tCHECKPOINT;
\n\tGO<\/font>\n\t<\/p>\n

\n\tobject_id   index_id    rscolid     rcmodified
\n\t———– ———– ———– ——————–
\n\t277576027   0           1           4
\n\t277576027   0           2           5
\n\t277576027   0           3           2
\n\t277576027   2           1           0
\n\t277576027   2           2           0
\n\t277576027   2           3           0
\n\t277576027   3           1           0
\n\t277576027   3           2           0<\/font>\n\t<\/p>\n

\n\tname             rowmodctr
\n\t—————- ———–
\n\tNULL             5
\n\tt1_c1_c2         3
\n\tt1_c3            1<\/font>\n\t<\/p>\n<\/blockquote>\n

\nSince the nonclustered indexes were created, I performed 3 updates to c1<\/font>, 2 updates to c2<\/font>, and 1 update to c3<\/font>. The sysrscols.rcmodified<\/font> 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.<\/font>\n<\/p>\n

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

\nI'll prove it by updating the statistics, doing 2 modifications to c1<\/font>, 4 modifications to c2<\/font> and checkpointing. We should see the sysrscols.rcmodified<\/font> for c1<\/font> go to 6, for c2<\/font> go to 9, and the sysindexes.rowmodctr<\/font> for t1_c1_c2<\/font> changes to 2.<\/font>\n<\/p>\n

\n

\n\tUPDATE STATISTICS t1;
\n\tGO<\/font>\n\t<\/p>\n

\n\tUPDATE t1 SET c1 = 7;
\n\tUPDATE t1 SET c1 = 8;
\n\tUPDATE t1 SET c2 = 4;
\n\tUPDATE t1 SET c2 = 5;
\n\tUPDATE t1 SET c2 = 6;
\n\tUPDATE t1 SET c2 = 7;
\n\tCHECKPOINT;
\n\tGO<\/font>\n\t<\/p>\n

\n\tobject_id   index_id    rscolid     rcmodified
\n\t———– ———– ———– ——————–
\n\t277576027   0           1           6
\n\t277576027   0           2           9
\n\t277576027   0           3           2
\n\t277576027   2           1           0
\n\t277576027   2           2           0
\n\t277576027   2           3           0
\n\t277576027   3           1           0
\n\t277576027   3           2           0<\/font>\n\t<\/p>\n

\n\tname             rowmodctr
\n\t—————- ———–
\n\tNULL             9
\n\tt1_c1_c2         2
\n\tt1_c3            0<\/font>\n\t<\/p>\n<\/blockquote>\n

\nLike so. Even though we updated c2<\/font> 4 times, the sysindexes.rowmodctr<\/font> for t1_c1_c2<\/font> is only set to 2, clearly using the sysrscols.rcmodified<\/font> delta for c1.<\/font>\n<\/p>\n

\nYou can play around with these examples to further convince yourself of the current behavior.<\/font>\n<\/p>\n

\nHope this has helped remove some confusion about this. There are unfortunately many, many twists to this and when counts are modified…<\/font>\n<\/p>\n

\nEnjoy playing around!<\/font>\n<\/p>\n

\n<\/font>\n<\/p>\n

\n<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,48,62,90],"tags":[],"class_list":["post-507","post","type-post","status-publish","format-standard","hentry","category-indexes-from-every-angle","category-inside-the-storage-engine","category-on-disk-structures","category-statistics"],"yoast_head":"\nHow are per-column modification counts tracked? - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How are per-column modification counts tracked? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2012-03-13T11:30:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:50:47+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/\",\"name\":\"How are per-column modification counts tracked? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2012-03-13T11:30:00+00:00\",\"dateModified\":\"2017-04-13T16:50:47+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How are per-column modification counts tracked?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How are per-column modification counts tracked? - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/","og_locale":"en_US","og_type":"article","og_title":"How are per-column modification counts tracked? - Paul S. Randal","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/","og_site_name":"Paul S. Randal","article_published_time":"2012-03-13T11:30:00+00:00","article_modified_time":"2017-04-13T16:50:47+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/","name":"How are per-column modification counts tracked? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2012-03-13T11:30:00+00:00","dateModified":"2017-04-13T16:50:47+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-are-per-column-modification-counts-tracked\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"How are per-column modification counts tracked?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/507","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=507"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/507\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=507"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=507"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=507"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}