You are here
Home > DMO

What Happened to rowmodctr from Sysindexes?


In my blog series Lost in Translation – Deprecated System Tables, I’ve been going through the compatibility views in SQL Server and translating and mapping them over to catalog views and dynamic management objects.  One of the ones that I was working on recently was mapping sysindexes to sys.indexes.  While working on that post, there is one question, from myself and others, that came up time and time again…

What happened to rowmodctr from Sysindexes?

When looking at the schema and definition for sys.indexes, you’ll notice that the column rowmodctr is not included in the catalog view.  Due to changes in the purpose of catalog views versus dynamic management views, it was no longer appropriate to store this value in the catalog view.  But then, where did it go?

Rowmodctr Definition

Before figuring out where the column went, let’s start by first looking at what the rowmodctr column provides.  According to Books Online, the column rowmodctr returns counts of how many rows have been inserted, updated, or deleted on tables.  Or to put it quite simply, the rowmodctr value is a measurement of change for the index.

One thing to note is that prior to SQL Server 2005, the value for rowmodctr was calculated slightly different than it is today.  BY this I mean that  that with SQL Server 2000, the value provided wouldn’t be quite the same as it is now in SQL Server 2012, or in 2005 or 2008.  Regardless of which calculation for rowmodctr your are looking for, the resulting value is nearly the same – so with either, you can continue reading along in this post and find a new manner to calculate rowmodctr.

Rowmodctr Purpose

It is, of course, useful to want to know why people are interested in rowmodctr and what it provides that is useful.  As mentioned earlier, rowmodctr measures the amount of change that has occurred to an index.  It’s a fairly generic measure of change though – for every insert, update, or delete – the value is incremented.  It doesn’t matter if the change was on all rows or a single row – change is change and the counter is incremented.

The reason that people do care about these values is that in conjunction with the STATS_DATE() function, the rowmodctr value can assist in statistics maintenance scenarios.  When considering large environments with many tables and many indexes and a high throughput of queries, getting the query plans right every time is kind of a big deal.  Statistics are critical in making this happen.  But when the volume of work to update all statistics is large, how do you dicide which statistics to update first?

The most often used solution is the STATS_DATE() function, but that function only tells you the age of the statistics.  Just being isn’t a definitive reason to update statistics.  But if you combine the age of the statistics with a metric identifying the amount of change on the table, then you can start to prioritize which statistics to update first.

Redefining Rowmodctr

We are now left with the problem that we know what rowmodctr is and why it is useful, but where did it go?  Is it still there and available to use?

As mentioned in the introduction, sys.indexes is a catalog view, which are primarily focused on storing metadata about database structures.  The information that provides a replacement to the rowmodctr value can instead be found in the dynamic management object sys.dm_db_index_operational_stats.

By using the columns leaf_insert_count, leaf_delete_count, leaf_update_count, and leaf_ghost_count, you arrive at all of the rows that are inserted, updated and deleted from a table or an index.  Through these, you can see the change that is happening and get an idea for what the rate of change is, along with the amount of data that has changed.  The query in LIsting 1 lists the rowmodctr calculation for all of the indexes in the database by using the DMO.

--Listing 1 – Rowmodctr from sys.dm_db_index_operational_stats

SELECT object_id
+ leaf_delete_count
+ leaf_update_count
+ leaf_ghost_count AS rowmodctr
FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL)

Wrapping Up

WIth this all detailed out, what are your thoughts?  Do you agree that you can use the DMO to get to the same information that was provided from rowmodctr?  Do you trust the results?  Let me know what you think in the comments below.


Ben Thul (Blog | @SpartanSQL), my former co-worker, pointed out the modification_counter in sys.dm_db_stats_properties.  This column can be used to do what the rowmodctr did in pre-SQL Server 2005 versions of SQL Server.  The catch with this DMO is that it is only available in SQL Server 2008 R2 Service Pack 2 and, soon to be, SQL Server 2012 Service Pack 1.

  • Ben Thul

    What about modification_counter in sys.dm_db_stats_properties?

    • StrateSQL

      I skipped it for now since it isn’t in SQL Server 2012 at this time. It will be a part of service pack 1 – I had figured I’d wait for it until then. But I should have probably left a comment about that in the conclusion.

  • Pingback: Lost in Translation - Deprecated System Tables - Introduction | Strate SQL()

  • Pingback: Lost in Translation - Deprecated System Tables - sysindexes | Strate SQL()

  • Peter Jacobs

    Very very useful…i was really looking for this for packaging an Update Statics

  • John Couch

    Why do the values from the two attributes not match? For instance, the results from these two queries should produce the same values for modifications, but they are way off from each other.

    • John Couch

      Even adding in the nonleaf values do not come close.

      • John Couch

        The other problem is that those values are not reset with a statistics update.

        • StrateSQL

          Apologies on the delay in responding. I’ve not had a chance to dig into this yet, but will give it a shot. What version of SQL Server are you using?

          • John Couch

            SQL Server 2008/2008 R2

  • Mark Freeman

    Given that the data provided by sys.dm_db_index_operational_stats is not reset when the statistics are updated, is there a real replacement for sysindexes.rowmodctr for SQL Server 2005 – 2008R2?