November 16, 2007 at 8:24 am
Does "Alter Index All Rebuild" rebuild just the tree level of the index? Or does it also rebuild the leaf level like reorganize?
Thanks and God Bless,
Thomas
ThomBeaux
Thomas LeBlanc, MVP Data Platform Consultant
November 19, 2007 at 2:19 am
It will rebuild the index and not reorganize.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 19, 2007 at 3:16 am
Rebuild is functionally the same as dropping the inex and recreting it. It rebuild all of the leaf and non-leaf levels of the index.
Reorg just removes fragmentation at the leaf level of the index, leaving the non-leaf levels untouched.
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
November 19, 2007 at 5:59 am
I can follow it for a non-cluster index, but a cluster index is the table itself. Rebuild rebuilds the whole table - tree and leaf nodes?
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
November 19, 2007 at 6:07 am
Yes, absolutely. IME, the worst fragmentation you get is at the leaf levels of a clustered index, and is the most necessary to rebuild. The higher levels of indexes generally don't get that badly fragmented, because there are fewer of them and more entries per page
As I mentioned, rebuild is functionally equivalent to DROP INDEX ... and CREATE INDEX ... (or CREATE INDEX ... WITH DROP_EXISTING)
If it only did the higher levels, then you'd have to do both a reorg and a rebuild to fully defrag an index, and that's a waste of typing. 🙂
Rebuild also updates the statistics on the index, something that reorg doesn't do.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply