Does "Alter Index All Rebuild"...

  • 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

  • It will rebuild the index and not reorganize.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 5 (of 5 total)

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