Post reply

Update Stats or Rebuild Index Strategies

  • August 5, 2010 at 8:50 am

    #1202814

    GilaMonster (8/4/2010)

    Tara Kizer (8/4/2010)

    That is only true when you pass from no rows to more than 0.

    Then after the first 500 and finally when around 20% plus 500 modifications have occurred which is the ongoing behavior for it.

    That's how the auto_update of stats behaves. It may be how sp_updatestats is supposed to behave, but it's not how it does behave. As I mentioned, in a presentation I did on stats last year I was able to update 10 rows in a 200 000 row table and when I reran sp_updatestats, it updated the stats on that table. The 20%+500 would be 40 500 rows, not 10. Drop me a PM with an email address I can use and we can take this to mail.

    As for the stats article, I've read that several times, along with the 2005 and 2000 versions. Mostly correct, but the bit about filtered stats is misleading (or was late last year, I don't know if they've rewritten).

    Yeah I made sure to question if what he gave me back was auto update or sp_updatestats, and he said it applies to both. I've got another DBA running some tests since the code of sp_updatestats doesn't reflect the info we received.

    Tara Kizer
    Microsoft MVP for Windows Server System - SQL Server
    Ramblings of a DBA (My SQL Server Blog)[/url]
    Subscribe to my blog

  • August 5, 2010 at 8:51 am

    #1202816

    TheSQLGuru (8/5/2010)

    Tara Kizer (8/4/2010)

    That is some bad code then. 😉

    I don't think it is bad code. Works fine to accomplish the objective. You may argue with the REQUIREMENT they implemented, which is to update stats on any table with at least one modification. But the name of the sproc is sp_updatestats, so it seems to fit the description. 😛

    It is bad code because BOL says that in 2005+, sp_updatestats only updates stats on those tables that have out of date stats. And clearly a table with a billion rows in it and only one row has changed doesn't have out of date stats.

    Tara Kizer
    Microsoft MVP for Windows Server System - SQL Server
    Ramblings of a DBA (My SQL Server Blog)[/url]
    Subscribe to my blog

Viewing 2 posts - 16 through 16 (of 16 total)

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