Indexing question

Question

Indexing question

vsamantha35

SSChampion

Points: 11551
More actions
July 2, 2019 at 12:18 pm

#3659236

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

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

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

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.

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