October 24, 2009 at 11:55 pm
Is it safe to say a Rebuild of an index also does a reorg, by default?
October 25, 2009 at 3:00 am
Technically no, but understand what the two do.
Reorg shuffles the pages at the leaf level of an index back into order. It doesn't touch the non-leaf levels and it doesn't change what's on the page.
Rebuild completely drops the old index and creates a new one. It'll try as best it can to put all the pages (leaf and non-leaf) in order and it will recreate all of the pages hence changing the free space on a page and other such things.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2009 at 6:06 pm
Gail
Although they are not the same, the end result of Rebuild also accomplishes (or includes) what is done through a reorg (rearranging leaf level pages), right?
Dan
October 25, 2009 at 9:18 pm
Not necessarily. The leaf pages are moved, but not upper level ones. Potentially that means you could have fragmented intermediate pages that don't get cleaned up. I suppose, depending on how leaf levels are moved, anything less than a full rebuild might not clean that up.
October 25, 2009 at 9:48 pm
OK... I'm confused... I thought that one of the major reasons for having a Clustered Index was so that it would reorg the data so that it wasn't fragmented and that a rebuild of the Clustered Index would do everything from defragmentation to "repacking" according to the Fill Factor as well as eliminating page splits... right down to the leaf level...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2009 at 1:54 am
repent_kog_is_near (10/25/2009)
Although they are not the same, the end result of Rebuild also accomplishes (or includes) what is done through a reorg (rearranging leaf level pages), right?
Both will fix the fragmentation at the leaf level.
Rebuild will also fix the fragmentation at the non-leaf levels, pack the rows into pages (according to fill factor) and update the index's statistics.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2009 at 1:56 am
Jeff Moden (10/25/2009)
OK... I'm confused... I thought that one of the major reasons for having a Clustered Index was so that it would reorg the data so that it wasn't fragmented
Huh? Don't understand, sorry.
and that a rebuild of the Clustered Index would do everything from defragmentation to "repacking" according to the Fill Factor as well as eliminating page splits... right down to the leaf level...
A rebuild will, yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2009 at 6:02 am
GilaMonster (10/26/2009)
Jeff Moden (10/25/2009)
OK... I'm confused... I thought that one of the major reasons for having a Clustered Index was so that it would reorg the data so that it wasn't fragmentedHuh? Don't understand, sorry.
and that a rebuild of the Clustered Index would do everything from defragmentation to "repacking" according to the Fill Factor as well as eliminating page splits... right down to the leaf level...
A rebuild will, yes.
Ah... never mind... the confusion was all mine. Serious misread on my part.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply