SQL Server Performance

Indexing recommendation

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by cncncn, Feb 28, 2007.

  1. cncncn New Member

    I have a sql server 2005 database with tables of varying size..from million rows to hundred rows. I want to reduce the maintanence window needed for the index rebuild.
    So I'm thinking of using the ONLINE option for some tables , so that I can reindex those tables during the day.
    But I can't do that for all the tables since "ONLINE option" is not a good option for rebuilding the large tables.So I need some recommendations for choosing the "ideal candidates" for online indexing.

    Any help in this regard is appreciated!
  2. MohammedU New Member

    Online index create or rebuild (ONLINE=ON) provides maximum concurrency, but uses more resources and takes longer to complete.
    You can also SORT_IN_TEMPDB option in your statement...if you use this option sorting will be done in tempdb...

    There are some restrictions for online indexing so...

    Read SQL Server 2005 Books Online "Guidelines for Performing Online Index Operations " for more info...

  3. satya Moderator

  4. merrillaldrich New Member

    You could change from reindexing to defrag + update statistics, so that there is no offline time. I just made this change to my system and it's working well.

    I have two jobs:

    1. Selective index defrag -- this one makes a list of indexes with fragmentation level above a certain value, and it has a time limit. During the time allowed, it defrags the indexes in order from the biggest/worst to the smallest/best until either the time runs out or it has defragged the whole list.

    2. Because defrag, rather than reindex, does not update stats, I also have a job with the same structure that will update statistics with fullscan, working from the stats objects with the most changed rows back to the ones with fewer changed rows, also with a time limit.

    Both jobs run during the night when there's less traffic on the system, but they are both online operations, so it remains available the whole time. If they don't finish completely, no problem; they just start up again the next night. And nothing happens during peak hours when the system is working hard.
  5. bryan42 New Member

    That sounds like a good plan. Could you share how you perform those two jobs? If they are custom scripts, is there any chance you could share them?
  6. Luis Martin Moderator

  7. MohammedU New Member

Share This Page