SP_UpdateStats

  • Hey Guys,

    I read this nice article from Grant on Simple Talk:

    http://www.simple-talk.com/sql/database-administration/grant-fritcheys-sql-server-howlers/

    In this article, Grant says that executing sp_updatestats will update the statistics again for those indexes also which have been Rebuild.

    Earlier, I believed (I think I read somewhere, don't remember where :-P) that statistics is updated only for those that need it & sp_updatestats doesn't update it for the recently rebuilded indexes.

    I am not suspecting on Grant, just want to make my assumptions clear on this point:cool:.

    Gail/Grant please help to clear this.


    Sujeet Singh

  • sp_update stats has threshold of rows that must have been changed since the last stats update for it to update them again. As Grant says in his article

    then, you run sp_updatestats… Which will go through the whole table, determining if any data has changed and the statistics need to be updated. “Any data” reads, one row. So if even a single row has been modified since the last time the statistics were updated

    Yes, that threshold is a single row.

    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
  • GilaMonster (1/12/2012)


    sp_update stats has threshold of rows that must have been changed since the last stats update for it to update them again. As Grant says in his article

    then, you run sp_updatestats… Which will go through the whole table, determining if any data has changed and the statistics need to be updated. “Any data” reads, one row. So if even a single row has been modified since the last time the statistics were updated

    Yes, that threshold is a single row.

    Thanks Gail, so it makes sense to run the maintenance in this order:

    1. sp_updatestats

    2. ALTER INDEX...REBUILD/REORGANIZE

    Thanks again 🙂


    Sujeet Singh

  • p.s. The maintenance plan 'Update Statistics' task doesn't use sp_updatestats. It runs an explicit UPDATE STATISTICS against every table, so no checks at all for whether or not even a single row has changed.

    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
  • Divine Flame (1/12/2012)


    Thanks Gail, so it makes sense to run the maintenance in this order:

    1. sp_updatestats

    2. ALTER INDEX...REBUILD/REORGANIZE

    Thanks again 🙂

    No, it doesn't. Why update statistics that are about to be updated?

    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
  • I wouldn't trust anything that guys says. He's a notorious crank.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (1/12/2012)


    I wouldn't trust anything that guys says. He's a notorious crank.

    lol...

    No, it doesn't. Why update statistics that are about to be updated?

    So how should I go about updating the statistics Gail , which order to folllow? Is there any guideline ?


    Sujeet Singh

  • How about only updating ones that haven't just been updated (by the index rebuild)? Yes, it's more work than blanket updating everything (maintenance plan) or updating with sampled if one row has changed (as sp_update stats does), but no free lunches here.

    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
  • If you're going to target the updates, I'd rebuild indexes that need it, then defrag those that need it, then update stats for those that were not rebuilt. But that requires you to take direct control. If you were using a Maintenance Plan, I'd reverse it and update stats first, then rebuild, then defrag. While you'll be updating some stats twice, at least you won't be stepping on clean updates.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • GilaMonster (1/12/2012)


    How about only updating ones that haven't just been updated (by the index rebuild)? Yes, it's more work than blanket updating everything (maintenance plan) or updating with sampled if one row has changed (as sp_update stats does), but no free lunches here.

    OK, I got it. Thanks a lot Grant for writing the article so that I got the better idea about sp_updatestas & thanks Gail for making it simple & clear :-).


    Sujeet Singh

  • excelent articel..

    thanks Grant 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 11 posts - 1 through 10 (of 10 total)

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