sysindexes | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

sysindexes

Hi, I was playing around with the ‘rowmodctr’ column in sysindexes table. I tried updating different columns in a table, but everytime the rowmodctr value is updated only for the Clustered index row in sysindexs. There are indexes on the columns that I’ve updated, but the rowmodctr values always shows ‘0’ for these indexes. Can anyone tell me why ? Also, I see negative rowmodctr values for some tables. What does these stand for ? Can anyone point me to some good documentation on this topic ?
From –
http://support.microsoft.com/default.aspx?scid=kb;EN-US;195565 sysindexes.rowmodctr column maintains a running total of all modifications to a table that, over time, can adversely affect the query processor’s decision making process. This counter is updated each time any of the following events occurs:
– A single row insert is made.
– A single row delete is made.
– An update to an indexed column is made. NOTE: TRUNCATE TABLE does not update rowmodctr. After table statistics have been updated, the rowmodctr value is reset to 0 and the table’s statistics schema version is updated.
Could this be the reason?
Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Thanks Gaurav, I did my testing on a table that just had an update of statistics. So, intially rowmodctr value was ‘0’ for all indexes. But, when I started updates on indexed (non-clustered) columns, the ‘rowmodctr’ value for the clustered index started increasing, where as the value for all other indexes stayed ‘0’. This is what I can’t understand. Shouldn’t the value increase on the coloumns that I updated ?
Try and run DBCC UPDATEUSAGE on the table or indexes.
I guess, since all the non-clustered indexes will be updated when the clustered index is updated, changing just the clustered index will be sufficient in the cases where clustered index columns have changes. It would bew interesting tio see what happens when you update just the fields which are part of non-clustered index and not clustered index. On the same lines see if something changes when you update non-indexed fields. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Where was my brain…When you have nonclustered indexes with NO Clustered Index SQL server uses the row identifiers as pointers to the indexes (nonclustered). When you have a clustered index the nonclustered indexes use the clustered indexes key value as pointers. If you are just "messing" around and its a developement database (which I hope) try and drop the clustered index, which will force the nonclustered indexes to use the row identifer as pointers then run your tests. See if this will cause all other values to increase. Tip thats why it is very important not to drop a clustered index in a Production environment. Instead use create index with drop existing. Which will not force SQL server to rebuild all pointers to nonclustered indexes.
I’ve dropped the clustered index and this time ‘rowmodctr’ value increased for the table (indid=0) itself. But, the indexes that I actually updated still shows ‘0’.
The SQL Server decides to update distribution statistics automatically by analyzing the value in the rowmodctr column in the sysindexes table.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Satya, so does this mean that ykchkri’s distribution Statistics wont be accurately updated since they show 0?
Well, in the case if already AUTO STATS are enabled there is a requirement to execute UPDATE STATS manually. As I’ve seen in my case having this option enabled will not fetch complete result and sometimes it can be achieved with manual intervention. Refer to Gaurav’s KBA link for explanation on this column. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hi, The whole purpose of this excercise is to create a script that manually updates the statistics based on the ‘rowmodctr’ value. So far, I’m doing it based on ‘Stats_date’ function. But, Someone in this forum gave me an idea to use ‘rowmodctr’ value. I want my script to update statistics for only those indexes that have a high ‘rowmodctr’ value. But, apparently this column is not very accurate. Did (or can) anyone try this test on your server ? I want to see if it is a problem only in my server or it is a Bug. If AUTO STATS is also going to use this ‘rowmodctr’ value, then that explains why AUTO STATS doesn’t always work properly.

As mentioned in the above article, AutoStat will automatically update the statistics for a particular table when a "change threshold" has been reached. This depends and only performs when the specified threshold is reached and causes the server to automatically generate all statistics required for the accurate optimization of a specific query. That is the reason we scheduled a weekly job to perform UPDATE STATS on the required table, though the AuTOSTATS is enabled. From one of the KBA Even if auto-update statistics is on, it is only triggered at certain thresholds. Any time you make significant changes to the amount or distribution of your data, Microsoft recommends that you manually execute an UPDATE STATISTICS statement. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Can someone explain the negative values for RowModCtr for non-clustered indexes? For example: SELECT RowCnt , RowModCtr , CAST(CAST((RowModCtr/(RowCnt*1.)*100) AS DEC(5,2)) AS VarChar(6)) + ‘%’ Delta , Name
FROM SysIndexes
WHERE ID IN ( OBJECT_ID(‘tblStats_Read’) , OBJECT_ID(‘Members_’) )
AND Name NOT LIKE ‘_wa%’
AND IndID <> 255 230544015 21948814 9.52% pk_cl_Members_MemberID_
136346359 5511 0.00% ix_ncl_unq_Members_List_UserNameLC_Domain_
164626860 -122557 -0.07% ix_ncl_Members_MemberType_
123397059 -79488 -0.06% ix_ncl_Members_UserNameLC_Domain
119717507 17473359 14.60% pk_tblStatsRead
103725071 0 0.00% ix_ncl_tblStats_Read_Stamp
103725071 0 0.00% ix_ncl_tblStats_Read_fkListID_MemberAction
103725071 0 0.00% ix_ncl_tblStats_Read_fkActionID
103725071 0 0.00% ix_ncl_tblStats_Read_fkLyrisMemberID_fkJobID_MemberAction_fkActionID
103725071 0 0.00% ix_ncl_tblStats_Read_fkLyrisMemberID_fkJobID_fkListID_MemberAction
103725071 0 0.00% ix_ncl_tblStats_Read_fkJobID_fkLyrisMemberID_MemberAction_fkActionID
103725071 0 0.00% ix_ncl_tblStats_Read_MemberAction_Stamp PS Hello, Satya!
Hi Jimmy, hope all well at your end.
BTW, how often you run UPDATE STATS and are AUTO_STATS enabled on the database? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Yes, AutoStats is enabled. So right now we’re at the mercy of such events in the middle of the dya. I have collected many examples of queries that would act a bit weird (typically returning results very slowly), only to find that an explicit UPDATE STATISTICS on the affected table allow the permitted the queries to return results as expected. I’ve thought about doing an UPDATE STATISTICS on our big, dynamic tables each morning. I don’t recall reading anywhere how to interpret negative numbers in RowModCtr. Do you think it is a function of outdated statistics?
Cant’ say this may be caused due to outdated statistics as they tend to slowdown the performance and if nothing as such at your end, then its ruled out. Even I’m unable to get appropriate information in this regard, posted the information to MS Support for reasonable reply. Hope to get information as per SLA at our end. May schedule the UPDATE STATS on that big table during less traffic hours. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I recently posted an item about an end of day job which varied from 1.5 hours to 6 or 7 hours for no reason. I believe the reason is because the statistics for one of the indexes was becoming out of date every 6 mins during the job execution. I needed to update stats due to rowmodctr in sysindexes table reach 10% of rowcnt. This improved locking to row locks rather than table locks. My question is, the index I mention above is a clustered index, if one of the columns of data is updated does this mean that the rowmodctr is incremented? Even if the column updated is not Key column of the index. i.e. Primary key. When is the Rowmodctr incremented? Does it make any difference if any of the columns are of data type Text or Image (blob) Thanks Jamie
UPDATE STATISTICS will be initiated by monitoring the sysindexes.rowmodctr value, which will be incremented upon each update. When it reaches or exceeds 500, you can expect UPDATE STATISTICS to be started. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Yes. But will the rowmodcntr be incremented if a column that is not the key column is updated?
Refer to the above KBA for Determining When AutoStat Generation Is Imminent description. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I have figured this out myself. The rowmodctr is incremented regardless of which column is updated. In my experience if the rowmodctr is >=8% of rowcnt the the level of blocking increases due to SQL Server deciding to use table rather than row locks. Never underestimate the value of haveing upto date stats.
]]>