Page History

...

In addition to reordering the leaf pages of the index, SQL defrag manager Defrag Manager compacts the data in the pages using the original fillfactor fill factor value specified for the table and then removes any empty pages. Consider the following conditions related to this compaction phase:

• Compaction is completely skipped if the Inhibit Page Locks property is set for the index.
• There are various algorithms built into the compaction phase to stop unnecessary work. For example, if the first page in the index is empty and all the other pages are full, SQL Server does not repeatedly move all the data forward one page.
• SQL Server compacts pages back to the fillfactor fill factor value defined for the index. Make sure this value is not set too high. For more information, see the SQL Server documentation.
• If a lock cannot be obtained on a page during the compaction phase of DBCC INDEXDEFRAG, SQL Server skips that page.

...

Interleaving occurs when an index extent, which is a group of eight index pages, is not physically contiguous because an extent for another index is intermingled with it. This condition can happen even when there is no logical fragmentation in the index. Although the pages may be physically and logically ordered, they are not necessarily contiguous. Switching between extents can impact performance as data access is inefficient. To resolve this issue, use SQL defrag manager Defrag Manager to rebuild the indexes to store them in contiguous pages and reduce the need to switch between extents.

...