Post reply

Update statistics

  • January 7, 2013 at 8:08 am

    #286996

    hello - i have bi-monthly maintenance plan that re-builds all the indexes of my databases where fragementation > 5%. my indexes are in good shape with this frequency.

    However, with the re-build of indexes, my statistics only for those indexes are updated when the indexes are rebuilt. Also, i have auto-stats and create stats on [ default options ].

    But my other statistics are not getting updated.

    Is is a good idea to run sp_updatestats for all my user dbs after the rebuild index job completes.

    This will make sure all my other stats that need a update will also be updated and since my re-indexing job will update stats for those indexes, sp_updatestats will not touch them since they have just been rebuilt by the rebuild index job.

    So, basically, 2 steps:

    1. Run rebuild index job for all Dbs for frag > 5%

    2. Run sp_updatestats for all dbs.

    please suggest on the approach,.

    Thanks

    K

  • January 7, 2013 at 8:14 am

    #1574337

    Don't use sp_updatestats, it does sampled updates (unless you tell it otherwise). If you are doing a maint plan to manage stats, run the stats updates with fullscan.

    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

  • January 7, 2013 at 9:45 am

    #1574398

    but UPDATE STATISTICS with FULL SCAN will scan for the entire table everytime and unnecessary update stats even if its not needed. i ran a samle update stats with full scan on few of my dbs and its taken a very long time [ 8-9 hours ] to complete, also its very resource intensive.

    whereas when I run sp_updatestats, it works intelligently and just updates stats on those stats where its required, though it does for sample updates, but it coves more than 60% of my statistics.

    Thoughts ?

  • January 7, 2013 at 10:31 am

    #1574424

    Kishore-132325 (1/7/2013)

    but UPDATE STATISTICS with FULL SCAN will scan for the entire table everytime

    Yup, that's what you need for accurate stats. You can run full scans on more important tables and sampled on less if you like. Not hard to add into a script if you know your tables well

    and unnecessary update stats even if its not needed.

    Then you can just do a check against sysindexes (yes, the deprecated one) for rowmodctr > 0 and only update those. That's all that sp_updatestats does.

    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

  • January 9, 2013 at 4:50 am

    #1575164

    Kishore-132325 (1/7/2013)

    but UPDATE STATISTICS with FULL SCAN will scan for the entire table everytime and unnecessary update stats even if its not needed. i ran a samle update stats with full scan on few of my dbs and its taken a very long time [ 8-9 hours ] to complete, also its very resource intensive.

    whereas when I run sp_updatestats, it works intelligently and just updates stats on those stats where its required, though it does for sample updates, but it coves more than 60% of my statistics.

    Thoughts ?

    Just remember, the level of requirement for the sp_updatestats is just a single row being modified. It's not that sophisticated a piece of code. You could follow Gail's advice and use the same approach.

    "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

Viewing 5 posts - 1 through 4 (of 4 total)

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