question

xnl28 avatar image
xnl28 asked

What is colmodctr?

Hello, colmodctr is mentioned in this [Technet article][1] describing when statistics are updated: > The number of rows in the table when the statistics were gathered was 500 > or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then. Later on, the article refers to rowmodctr. Now, I know rowmodctr is a field in sys.sysindexes and described by BOL as "Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table." Is colmodctr available for inspection from any system view? Or is colmodctr actually just a typo of rowmodctr? [1]: http://technet.microsoft.com/en-us/library/dd535534(v=sql.100).aspx
sql-server-2008-r2statistics
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The site runs on votes. For each answer that was helpful you should indicate this by clicking on the thumbs up next to that answer. For any answer that lead to a complete solution, you should indicate that one answer by clicking on the check mark next to it.
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
My understanding is that rowmodctr is internal to SQL Server and the user doesn't have any way to view this data.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rowmodctr is available from sys.sysindexes
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
As far as I know, the colmodctr is an internal only value. We can't see it. It is not a typo of rowmodctr, because, for statistics, you need to know which column has been modified, not just which row.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

xnl28 avatar image
xnl28 answered
I have found [this article][1] which describes how to view colmodctr in SQL Server 2008 R2. You have to connect via a dedicated administrator connection to do so! And I assume that colmodctr is in the sys.sysrscols.rcmodified column. SELECT SO.NAME AS tableName, COL_NAME(sc.object_id, sc.column_id) AS columnName, A.name as stats_name, SSC.*, SSR.* FROM sys.sysrscols SSC INNER JOIN sys.sysrowsets SSR ON SSC.rsid = SSR.rowsetID INNER JOIN sys.sysobjects SO ON SSR.idmajor = SO.id INNER JOIN sys.stats_columns SC on SSR.idmajor = SC.object_id AND SSC.rscolid = SC.column_id INNER JOIN sys.stats as A ON A.object_id = SO.id WHERE SO.xtype = 'U' [1]: http://www.sqlservercentral.com/blogs/sql-toast/2013/09/29/sql-server-statistics/#comments
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Interesting. Not sure how applicable it is to day to day operations, but good to know it's out there.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.