Replacement for rowmodctr in sys.sysindexes?

Question

Replacement for rowmodctr in sys.sysindexes?

SQL Guy 1

SSCoach

Points: 15817
More actions
December 13, 2017 at 12:23 pm

#330531

I have a proc where I update stats based on rowmodctr value in sys.sysindexes. But because this view is deprecated I want to find a replacement. I found sys.dm_db_stats_properties function, but I need to get object_id() and call it in a cursor for each index/statistics so it's inconvenient to use in queries.
But is there any view or DMV with this value?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

SQL Guy 1 - Wednesday, December 13, 2017 12:23 PM
I have a proc where I update stats based on rowmodctr value in sys.sysindexes. But because this view is deprecated I want to find a replacement. I found sys.dm_db_stats_properties function, but I need to get object_id() and call it in a cursor for each index/statistics so it's inconvenient to use in queries.
But is there any view or DMV with this value?
Thanks

Colmodctr is supposed to be the replacement but I don't believe it's exposed in any DMVs, system tables. Didn't used to be anyway.
The view sys.system_internals_partition_columns has some counts on the changes but I believe it's more of an approximation. Take a look at this articles as it goes into some of this and may give you some other ideas:
How are per-column modification counts tracked?

Sue

SQL Guy 1 SSCoach Points: 15817 More actions · Answer 2

Thanks, Sue for pointing to a sys.system_internals_partition_columns but unfortunately its modified_count does not reset after I run UPDATE STATISTICS. Besides, it is selecting much slower than outgoing sys.sysindexes.

Paul in his article refers to a hidden/undocumented sys.sysrscols which I even cannot access - I have not full DBA privileges on server, so I can't use it in applications.

Will continue searching further.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It's the column 'modification_counter' in sys.dm_db_stats_properties.

You don't need to use a cursor for object ID and stats ids. You can use CROSS APPLY to pass them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

SQL Guy 1 - Wednesday, December 13, 2017 12:23 PM

...I found sys.dm_db_stats_properties function, but I need to get object_id() and call it in a cursor for each index/statistics

no need for cursors, you can use this function with a CROSS APPLY like so:
SELECT OBJECT_SCHEMA_NAME(i.object_id, DB_ID()) AS SchemaName, OBJECT_NAME(i.object_id) AS TableName, i.index_id, i.name AS IndexName, i.is_primary_key, i.is_unique, sp.last_updated, sp.modification_counter, sp.rows FROM sys.indexes i CROSS APPLY sys.dm_db_stats_properties (i.object_id, i.index_id) sp WHERE OBJECT_SCHEMA_NAME(i.object_id, DB_ID()) <> 'sys' AND sp.modification_counter > 0 ORDER BY SchemaName, TableName, i.index_id;

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Heh... although RowModCtr is deprecated, it's still what is used by Microsoft in the sp_updatestats sproc even in SQL Server 2016.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Guy 1 SSCoach Points: 15817 More actions · Answer 6

Thanks all for valuable replies. I modified the query in this way:

SELECT --t.object_id,
        s.name,
        t.name,
        a.name,
        sp.last_updated,
        sp.modification_counter,
        sp.rows,
        modified_percent = sp.modification_counter*100.0/sp.rows
FROM sys.schemas s
        join sys.tables t on s.schema_id = t.schema_id
        join sys.stats a on t.object_id = a.object_id
        CROSS APPLY sys.dm_db_stats_properties (t.object_id, a.stats_id) sp
WHERE t.name = 'my_table_name'

Now another question, because I have both total rows and modified rows, what would be better criteria to update statistics: modification_counter > 0 or modified_percent > 0.5% ? Or some another value?

I am writing this code not for regular nightly/weekly maintenance but for application and SSIS, and want this code to have minimal performance impact. Average daily load for some tables ~= 200 mln rows.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

SQL Guy 1 - Thursday, December 14, 2017 7:57 AM
Thanks all for valuable replies. I modified the query in this way:
SELECT --t.object_id,
        s.name,
        t.name,
        a.name,
        sp.last_updated,
        sp.modification_counter,
        sp.rows,
        modified_percent = sp.modification_counter*100.0/sp.rows
FROM sys.schemas s
        join sys.tables t on s.schema_id = t.schema_id
        join sys.stats a on t.object_id = a.object_id
        CROSS APPLY sys.dm_db_stats_properties (t.object_id, a.stats_id) sp
WHERE t.name = 'my_table_name'
Now another question, because I have both total rows and modified rows, what would be better criteria to update statistics: modification_counter > 0 or modified_percent > 0.5% ? Or some another value?
I am writing this code not for regular nightly/weekly maintenance but for application and SSIS, and want this code to have minimal performance impact. Average daily load for some tables ~= 200 mln rows.

If the table is truncated and reloaded every day, you probably don't have to worry about stats at all... it's likely they'll update all by themselves. In 2014 (IIRC), if the 200 million rows is more than 20% of the table, stats will automatically update unless you have auto updates turned off. Again IIRC, the stats rebuilds in either case will be based on what SQL Server wants to use for a sample. If you don't like the sample size (which can be terribly small), then consider doing a full sample because the difference between doing a full sample and even as little as a 25% sample is frequently a tie.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)