Post reply

Update Stats (SQL 2000)

  • June 27, 2009 at 8:19 am

    #93762

    I know in SQL Server 2005 / 2008 the sp_updatestats is optimized to perform work only on the statistics that need to be updated, but this doesn't seem to be the case in 2000. I was doing some research online regarding how to figure out which stats need updating and I'm not entirely clear on one thing in particular... If you have a STATS_DATE of NULL, does that always mean you should skip running update stats on an object?

    For instance, if I have a heap table (index ID 0) and there are no specific statistics created on that table, do I ever want to run update stats on it?

    Do I want to go solely by the number of records in the table and the rowmodctr in sysindexes or do I want to go by that AND the STATS_DATE() value?

    Any guidance is appreciated, thanks

    John

  • June 27, 2009 at 9:46 am

    #1016406

    John (6/27/2009)

    If you have a STATS_DATE of NULL, does that always mean you should skip running update stats on an object?

    As far as I'm aware, Stats_date of NULL means that there are no stats. It's possible on Index 0 (since that isn't actually an index)

    For instance, if I have a heap table (index ID 0) and there are no specific statistics created on that table, do I ever want to run update stats on it?

    No, because there are no stats to update. Check for system created ones though

    Do I want to go solely by the number of records in the table and the rowmodctr in sysindexes or do I want to go by that AND the STATS_DATE() value?

    Bear in mind that if autoupdate stats is on, SQL will automatically update the stats when the number in rowmodctr exceeds a threshold (20% of the table + 500 rows). The only time you need to manually update is for tables/indexes/stats where you know that the auto update threshold is too high.

    You can go by stats date, but that's only telling you how old the stats are, not how inaccurate they may be. Imagine a lookup table that's never updated. It's no concern if the statsdate for that table is a year ago. Since the table never changes, the stats are still valid.

    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

  • June 27, 2009 at 11:17 am

    #1016419

    That makes sense. Thank you for your help

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

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