Post reply

Rebuild index maintenance plan steps

  • July 13, 2010 at 8:29 am

    #237674

    Hi all,

    I not the most familiar with SQL server and i know a few threads have been started regarding rebuilding the indexes in SQL.

    If i would just like to create a maintenance plan to rebuild all DB indexes, is it pretty much straight forward through the maintenance plan wizard ? Or should I take some things into consideration doing it this way.

    Much appreciated

  • July 13, 2010 at 9:07 am

    #1192109

    The maintenance plan wizard is pretty straight forward. Just remember that it will go through and reindex everything. How large are the databases that you are working with? One thing that may be a concern is that if you are working with large databases (25GB+) you may experience periods of extended blocking. You can minimize that by either doing the re-index off hours or doing a defrag instead of a rebuild.

    If you have enterprise edition you can try using the online re-index option which helps avoid blocking issues.

  • July 13, 2010 at 9:21 am

    #1192137

    thanks for your answer...

    We have the standard ED, and our DB is around 8 GB.

    I ran this command to verify the tables and their frag level and it produced a lot of frag 99% on some tables.

    Should I be looking for details in another column ?

    SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) order by avg_fragmentation_in_percent desc;

  • July 13, 2010 at 10:44 am

    #1192222

    If you want to write your own proc then you can program it to only re-index if the logical frag and scan density are above certain thresholds (maybe 15% fragmented and 85 or less scan density). That way tables that are not fragmented will be skipped.

    Those are typically the two numbers I look at.

    John

  • July 13, 2010 at 11:10 am

    #1192239

    Actually I don't want to write my own Proc 🙂

    I just want to keep it simple and leave it up to the system during off-hour time periods.

    So basically just create a plan with rebuild indexes / re-organize and update stats .

    Is this OK with basic parameters ?

  • July 13, 2010 at 12:40 pm

    #1192283

    I would create one maintenance plan for re-index and a separate one for update stats. Then schedule them when needed.

    That should do it.

  • July 13, 2010 at 12:42 pm

    #1192285

    Ok will do ...thanks for your help

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

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