Indexing question

  • vsamantha35

    SSChampion

    Points: 11170

    Hi All,

    What is the main difference between normal index rebuild and online index rebuilds? is there any performance benefits ? does it avoid blocking on large tables ?

    What actually happens behind in offline index rebuild and online index rebuild.

    Trying to see if anyone can explain in layman terms. Its quite overwhelming if I look at the documentation, that's why wanted to see for simple understandable explanation.

    Thanks,

    Sam

  • Jeff Moden

    SSC Guru

    Points: 996828

    For both online and offline index REBUILDs (and assuming the index is over 128 extents (or 1024 pages), which is only 8MB), a brand new index will be created and the old one won't be dropped until the new one commits.

    If the REBUILD is done with (ONLINE=ON), then a 3rd object is built behind the scenes to handle any changes that occur to the data in the index while the index is being rebuilt.  Once the new index is built, the changes will be applied.

    The ONLINE=ON option is quite a bit slower than ONLINE=OFF because (supposedly... I've not taken the time to confirm) it's checking for changes in the process even if no changes occur.  My personal observation is that, for some reason that I've also not taken the time to look into, ONLINE=ON also doesn't do quite as good a job at rebuilding the index either.

    To summarize... my personal observations have been that, if I can, I'll use WITH (ONLINE=OFF) whenever I can because its so much faster and does a better job of rebuilding than WITH (ONLINE=ON).

    As a bit of a sidebar, I also NEVER use REORGANIZE except when I need to compact LOBs, which is NEVER because I do other things to avoid even that.  REORGANIZE is second only to DBCC SHRINKFILE in screwing up indexes.  If I don't have the time to REBUILD an index and can't do it ONLINE, I'll just leave it be until I can REBUILD it rather than screw it or my system up with REORGANIZE.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720425

  • vsamantha35

    SSChampion

    Points: 11170

    Thank you Jeff.

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

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