fluffydeadangel (12/20/2012)
George M Parker (12/20/2012)
The primary difference is internal fragmentation versus external fragmentation. The Reorganize fixes external fragmentation when the 8k pages are physically out of sequence. The Rebuild fixes internal fragmentation when there is a lot of empty space available on the 8k pages.So, unless I'm pulling detailed reports and doing these by hand, i'm guessing it's best to do a reorg first, and fix what's left with a rebuild? I will say i was unaware of those differences, you've given me something new to look into.
I have no issues doing these by hand, and it's always good to keep an eye on what breaks and how.
If you have the luxury of being able to restore the database to another location, then I would run the DMV in DETAILED mode on the restored copy and review the internal fragmentation data there.
Our maintenance process uses a table driven process to determine which indexes to rebuild or reorganize. It's coded in such a way that if an index is reorganized more than 3 times in a row, the next cycle rebuilds the index. We found that we were addressing the external fragmenation but never the internal fragmentation and therefore modified the code accordingly. And of course, any index that is reorganized is then flagged for an UPDATE STATISTICS to be performed since reorganizing an index does nothing to the statistics.
And since we track when an index is rebuilt versus reorganized, we have a better idea of what fillfactors might need to be adjusted.