What is the difference between reorg defrag and reorg rebuild online ?

[Former Member]

Both seems to server same purpose. Why there are 2 different command ?

[former_member182259 · Accepted solution]

Actually, there is a major difference (and besides the fact that reorg rebuild can work on an index whereas reorg defrag is limited to the full table) in how they function.   Reorg rebuild - as you are well familiar - copies the entire table to *new* pages, row-by-row if you will.   Reorg defrag works similar to reorg compact/forwarded_rows in that it works within the *existing* pages.   One diff, I think (would have to check to be sure) is that while reorg compact/forwarded rows only worked within an extent at a time, reorg defrag works within the full allocation unit - which would allow it to do a better job as working within an extent could allow sparse extents and not much space savings where as working within the allocation unit means that worst case there is one sparse extent.

As a result, reorg rebuild needs a lot of empty space in order to work - while reorg defrag probably needs extremely little if any.

I would also caution you on reorgs.   If reorg'ing for forwarded rows only, then after setting the expected row size, reorg defrag may be all that is necessary and much faster than reorg rebuild as the entire index tree may not need to be rebuilt.    If reorg'ing due to a lot of deletes (and HKGC is already set to 5), then assuming that the deletes were at the "front" of the table due to archiving, a timed reorg rebuild may be all you want to do - and just run long enough to collapse the space - e.g. run for 10-15 mins at a crack until space is reclaimed.    You can run on entire table with online - but you might need a ton of free space plus a lot more log space (depends on sp_dboption settings).

If reorging due to data cluster ratios - weeelllllllllll.......to me, this is kinda one of those pipe dreams.  I might give you what you think you want, but in reality, the reason why the data CR had an impact is because the indexing likely isn't what it should be and we were doing a leaf scan or range scan in the leafs due to partial index usage or using an index that wasn't very selective based on the predicates.   Fixing the index is the better long term solution     If index CR, then simply run reorg rebuild on that index.

[Former Member · Accepted solution]

Thanks Jeff.