November 14, 2011 at 3:17 pm
Greetings!
Is is possible to rebuild a fragmented clustered index with (online=on) option?
Thank you for your responses
November 14, 2011 at 3:18 pm
I think you can do this on Enterprise Edition only.
November 14, 2011 at 3:22 pm
Yes it is Enterprise edition...
and I am rebuilding non-clustered indexes online no issues
Just trying to figure out the mechanics of how a Clustered index can be built online considering that the leaf of a clustered index stores the table data....
If someone can guide me to any resources it would be very helpful
Thanks
November 14, 2011 at 3:26 pm
Basically, take a schema lock and bump the schema version.
Build a copy of the index. Keep it in sync with the dml in the base table.
Once done swap in place of the old one and drop.
That's why you need 120%+ of free space of the CI to be able to do the rebuild.
November 14, 2011 at 3:40 pm
Nice! Thanks Ninja
November 15, 2011 at 1:28 am
anish_ns (11/14/2011)
Just trying to figure out the mechanics of how a Clustered index can be built online considering that the leaf of a clustered index stores the table data....
Exactly the same way as a nonclustered index. There's virtually no difference between the two when you get to the level of index pages.
Providing there are no LOB columns, the index can be rebuilt online.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply