Post reply

Why should I run update statistics after rebuilding the indexes on a table?

  • June 13, 2011 at 9:41 am

    #241284

    Hi everyone,

    To enhance the performance I was asked to run update statistics on a table with full scan after rebuilding the indexes. Aren't the statistics upto date once we rebuild the indexes? Is it necessary to update the statistics too after rebuilding the index?

    If I need to do both(rebuild index as well as update statistics), could someone please explain why do i need to do so?

    Thank you.

  • June 13, 2011 at 9:45 am

    #1337856

    You shouldn't.

    What you may need to do is update the column statistics (ones not associated with indexes) as they would not have been updated by index rebuilds.

    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 13, 2011 at 9:45 am

    #1337857

    My understanding is that the stats are updated only for the index that you rebuilt, not the columns "beside" the index.

    Can anyone confirm this?

  • June 13, 2011 at 9:46 am

    #1337858

    Tx Gail... now that's quick service :w00t:.

  • June 13, 2011 at 9:49 am

    #1337860

    I live to anticipate. 😀

    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 13, 2011 at 9:57 am

    #1337870

    GilaMonster (6/13/2011)

    I live to anticipate. 😀

    Must be all that martial arts training! Getting to the Jedi level now :w00t:.

  • June 13, 2011 at 10:28 am

    #1337891

    Gail, don't you think it becomes really tough to write a query to update statistics if there are 200 columns.

    For example: I need to specify 197 columns in the update stats query if there are indexes on 3 columns. Am I correct? So is it better to just do update stats with full scan on the whole table rather mentioning all 197 columns?

  • June 13, 2011 at 10:33 am

    #1337894

    sunny.tjk (6/13/2011)

    Gail, don't you think it becomes really tough to write a query to update statistics if there are 200 columns.

    For example: I need to specify 197 columns in the update stats query if there are indexes on 3 columns. Am I correct? So is it better to just do update stats with full scan on the whole table rather mentioning all 197 columns?

    It is fairly easy to do with a maintenance plan.

    In an update statistics maintenance plan task, you can specify that you want index statistics, column statistics, or both.

Viewing 8 posts - 1 through 7 (of 7 total)

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