locked
Index Rebuild Vs Reorganize...? RRS feed

  • Question

  • HI Guys ..

      Just wanted to know how often should do Rebuild Index and Reorganize Index in week or what are the best practices for Index Maintenance ? 

    - Need to update Statistics after reorganize Index separably in Maintenance plan?

    - i am going to do Reorganize Index Every night and Rebuild once in sunday night so is it good one?

    - Any good script ?


    Ankit Shah SQL Server DBA


    • Edited by -kit Friday, July 27, 2012 6:43 PM
    Friday, July 27, 2012 6:42 PM

Answers

  • I disagree with the 30% rule. It depends.... just like many things in RDBMS.

    If you have a choice, then REBUILD.

    REORGANIZE can take much longer than REBUILD, but if you have no choice, you have to go with it. Pay attention to execution time though.

    You should REBUILD indexes every weekend, UPDATE STATISTICS every night.

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth SQL SERVER 2012 & BI TRAINING


    Saturday, July 28, 2012 1:00 AM
    Answerer
  • Best practices:

    Daily: Update Stats followed by recompile to force regenerate new plan as per new statistics.
    (You may need to observe the performance on your server. )

    Weekly: Rebulid or ReIndex all Indexes on the basis of fragmentation value. Followed by Recompile.


    Kindly mark the reply as answer if they help

    • Marked as answer by amber zhang Friday, August 3, 2012 1:48 AM
    Tuesday, July 31, 2012 12:06 PM

All replies

  • Hi,

    As a recommanded practice if fragmentation is below 30% the reorganize else rebuild. The best script for this Ola Hallengren's utility..You can schedule a job to call this procedure..

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    - Chintak (My Blog)

    Friday, July 27, 2012 6:49 PM
  • I disagree with the 30% rule. It depends.... just like many things in RDBMS.

    If you have a choice, then REBUILD.

    REORGANIZE can take much longer than REBUILD, but if you have no choice, you have to go with it. Pay attention to execution time though.

    You should REBUILD indexes every weekend, UPDATE STATISTICS every night.

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth SQL SERVER 2012 & BI TRAINING


    Saturday, July 28, 2012 1:00 AM
    Answerer
  • Ankit,

     

    Please check this link for DIFFERENCE BETWEEN INDEX REBUILD AND INDEX REORGANIZE

     



    Nareshkar Pakanati 

    MCTS, MCITP IT Specialist
    My Blog: www.mssqlfix.com
    Twitter: @mssqlfix
    -------------------------------------------------------
    Please mark the post as Answered if it answers your question.
    -------------------------------------------------------

    Tuesday, July 31, 2012 9:59 AM
  • Best practices:

    Daily: Update Stats followed by recompile to force regenerate new plan as per new statistics.
    (You may need to observe the performance on your server. )

    Weekly: Rebulid or ReIndex all Indexes on the basis of fragmentation value. Followed by Recompile.


    Kindly mark the reply as answer if they help

    • Marked as answer by amber zhang Friday, August 3, 2012 1:48 AM
    Tuesday, July 31, 2012 12:06 PM
  • Hi

    Referring this might be a good idea for understanding

    Rebuild Vs Reorganize


    Thanks Saurabh Sinha http://saurabhsinhainblogs.blogspot.in/ Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, February 4, 2014 6:17 PM