August 30, 2011 at 7:55 am
Hello all,
I've read that rowmodctr in sys.sysindexes is no longer accurate (SQL2005 and later), which does appear to be accurate after comparing sys.sysrscols (via DAC). With that said, how does one determine how statistics have become stale?
While yes, AUTO UPDATE STATISTICS "should" take care of this, I simply do not agree. Point in case, every Sunday I run an UPDATE STATISTICS WITH FULLSCAN on all databases and tables. This past Sunday it ran on a table with 10,013,516 records. AUTO UPDATE is enabled on this database and in the last 58 hours, no update has occured. Rowmodctr shows 2,139,240,592 whereas sys.sysrscols shows 90,253,746. Regardless to as which is right or wrong, the AUTO UPDATE is not firing.
The above combined with the simple fact that I want to build a dynamic routine based on some threshold(s) to run daily is the purpose of wanting to find additional information to as how this data is stored and how to determine when/how to update stats on my own.
So pending on what is discovered based on the above, I also need to determine when and how to run UPDATE STATS WITH SAMPLE X PERCENT. Currently my thought process would simply be based either on the amount of rows in the table and/or rowmodctr (if it can be proven to be accurate).
So the original idea was something as such:
- If percent change (rowmodctr / rowcount) >= 5% - update stats
- From here ... is where I need to find my logic. What threshold determines what level of update to run? FULLSCAN vs. SAMPLE
Side question ... why is it that stats can be created on a HEAP; however when you update the stats, nothing changes (rowmodctr, sysrscols and stats_date). sp_updatestats ignores index_id = 0 ... but I'm curious to as how/why they're created and you can update them ... but they appear to have no effect.
Any insight on this matter would be most helpful. If done properly, I feel that I can build a procedure to run daily to keep our statistics at peak performance without having to explicitly always do FULLSCANS thus bogging down the target system.
Thank you
August 31, 2011 at 4:18 am
It's really difficult to answer this because I think it's a case of what suits your system. I run a sp_updatestats with resample every night at a quiet time, I don't have any time when users are not in the system. I don't consider auto update stats, it's on but I doubt it does that much as the %age changes required for main tables to get an auto update will be greater than ever occurs. for small tables I don't care! I have async on anyway to minimise any issues which I've never seen.
Don't know id this helps or not? I've never noticed issues with row counts, but you can always use dbcc updateusage(0) with count_rows;
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply