Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

While index rebuild will require at least same amount of space in the data file as current index index (as a copy of the index will be created)

My reading on SQL Server BOL related to reorg it only involves leaf level, and only needs a single 8KB Page,

Can anybody explains how reorg works (internals) in simple technical terms..?

share|improve this question
1  
Index rebuilds only require at least the same amount of space as the current index for OFFLINE operations. An ONLINE non-clustered index rebuild requires only version-store space (in tempdb), and this depends on the level of concurrent modifications during the rebuild. An ONLINE clustered index build might require version store space for the same reason, but it also needs space for a temporary mapping index (to map old bookmarks to new.) This typically requires 15-30% of the size of the index. Whether the mapping space uses tempdb or the user database depending on the SORT_IN_TEMPDB option. –  Paul White Jan 16 '12 at 11:35
add comment

2 Answers

up vote 8 down vote accepted

This is a précis of the relevant section in the SQL Server 2008 internals book.

SQL Server first acquires an IX lock on the index (or index partition).

The reorganization then (assuming the LOB_COMPACTION option is not OFF) compacts any LOB pages belonging to the index by moving pages out of low density uniform extents (less than 75% / 6 pages full) into other uniform extents with free space.

Then it moves onto the leaf pages of the index.

The simplified description in the book is that it first determines the first logical page and the first physical page. If different it takes X locks out on both pages and swaps them using one additional new page as a temporary storage area. Then it does the same for the second physical/logical pair and so on.

It is somewhat more complicated than that however. The book mentions that it also looks at a sliding window of eight logically consecutive pages and determines whether rows can be moved around between these pages to allow a single page to be emptied and removed (while taking into account the index fillfactor).

As re-organization does not allocate new pages to the index it cannot reapply the fill factor if the pages are fuller than required however. Additionally no defragmentation is carried out for pages in mixed extents.

share|improve this answer
add comment

Just for the info I kept searching for this answer... there was a blog on MSDN had some information about but fragmentation but in that blog there was a link to article written by Kalen Delaney on November 27, 2000 She explains in a concise and clear way internals of index reorg. (you may have to scroll down a bit). Article is almost 11 years old but I believe DBCC IndexDeFrag may be using same algorithm as alter index reorg. The way I understood it swaps pages to remove logical fragmentation.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.