Sql-server – SQL Server index reorg

sql-server-2008

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..?

Best Answer

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.

Related Question