Post reply

The value returned from sys.sysindexes in the rowmodctr column for HEAPS.

  • October 27, 2014 at 6:40 pm

    #291438

    Hi,

    Just wondering if anyone can clarify the value that is returned from sys.sysindexes in the rowmodctr column for HEAPS.

    I have been doing some testing and I can see when I update all of the statistics on a HEAP the value in this column goes to 0 for all Statistics but never for the Heap record itself (indid = 0). The value returned is either the same as the rowcnt or near enough.

    Spent some time going through Paul Randalls post below but couldnt come up with the answer,

    http://www.sqlskills.com/blogs/paul/how-are-per-column-modification-counts-tracked/

    Cheers!

  • October 28, 2014 at 7:52 am

    #1755222

    Interesting stuff. I did some playing around and on my heap table (which just has 1 record, and four columns) it has a rowmodctr of 137. It didn't have any auto-generated statistics, so I manually created them and updated them, still no dice. Then I rebuilt the heap, using ALTER TABLE ... REBUILD, and funny, it didn't zero out the rowmodctr...in fact it incremented it to 138!

    Just for clarity's sake you mention sysindexes in subject and sys.indexes in the body, the latter catalog view doesn't have a rowmodctr so I assume you're talking about the 2000-era compat view sysindexes. I've seen weird stuff from these compatibility views at times...

    Anyway I'm curious now, too.

    EDIT: sys.dm_db_stats_properties has a modification counter that seems to be more accurately maintained (sysindexes is just providing an estimate, according to BOL, and does not guarantee correctness) but still, doesn't answer your question of when and how that counter resets on a heap. I've got one heap that has a count of 222,313,527 in that field. I'm about a tenth of the way to overflowing that int! 😀

  • October 28, 2014 at 5:37 pm

    #1755387

    Thanks for getting back - yes still no closer to finding an answer to this one - Apologies for the confusion also. Yes I am referring to sys.sysindexes. If I get anywhere with this I will update the post...

Viewing 3 posts - 1 through 2 (of 2 total)

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