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 ,index_id ,leaf_insert_count + 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.
Follow-Up
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.
Related posts:
- Decoding first, root, and FirstIAM in Sysindexes
- Discovering Binary Pages In Sys.Indexes
- Lost in Translation – Deprecated System Tables – sysfulltextcatalogs
