Rebuilding a single table or partition index rewrites all index rows to new pages. This improves performance by:
Improving clustering of the leaf level of the index
Applying stored values for the fill factor on the index, which can reduce page splits
Applying any stored value for reservepagegap, which can help reserve pages for future splits
To reduce contention with users whose queries must use the index, reorg rebuild locks a small number of pages at a time. Rebuilding an index is a series of independent transactions, with some independent, nested transactions. Approximately 32 pages are rebuilt in each nested transaction and approximately 256 pages are rebuilt in each outer transaction. Address locks are acquired on the pages being modified and are released at the end of the top action. The pages deallocated in a transaction are not available for reuse until the next transaction begins.
If the reorg rebuild command stops running, the transactions that are already committed are not rolled back. Therefore, the part that has been reorganized is well-clustered with desired space utilization, and the part that has not been reorganized is the same as it was before you ran the command. The index remains logically consistent.
Rebuilding the clustered index does not affect the data
pages of the table. It only affects the leaf pages and higher index
levels. Non-leaf pages above level 1 are not rebuilt.
