Post reply

Update statistics Vs. ReIndex

  • March 24, 2011 at 12:10 pm

    #248632

    I've a table having multiple non clustered indexes and I've multiple reports based on this table data.

    When I Insert large volume of data in this table to improve the performance either I need to update the statistics of those indexes or use reindex command to rebuild the indexes.

    Which is the best option to improve the performance either update statistics or rebuild the indexes.

    Any help is appreciated

    Thanks in advance!!

  • March 24, 2011 at 12:41 pm

    #1303168

    It really depends. The better option would be to implement a routine that rebuilds or reorganizes the indexes based on fragmentation level. After the index maintenance then you could update statistics.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 24, 2011 at 2:17 pm

    #1303226

    Keep in mind when updating statistics, don't include the statistics of the indexes you've just rebuilt !

    An index rebuild does a 100% scan and stats update for this index. ( indexdefrag does not do this )

    Chances are your update statistics statement doesn't use 100% scan, so will provide less accurate stats.

    A nice ref is the (free downloadable at RedGate) ebook "sql server statistics" by Holger Schmeling

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data and code to get the best help

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Who am I ? Sometimes this is me but most of the time this is me

  • March 25, 2011 at 9:27 pm

    #1303921

    Thanks for your reply..

  • March 25, 2011 at 9:28 pm

    #1303922

    Thanks for quick your reply..

  • March 27, 2011 at 2:08 pm

    #1304116

    You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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