Based on the coverage settings you select, SQL Defrag Manager defragments tables and indexes in one of the subsequent ways:
The rebuild defragmentation type uses the DBCC DBREINDEX command to rebuild the indicators on the tables. The rebuild operation creates fresh, contiguous pages. SQL Server 2005/2008 enables the choice to Rebuild Online, which permits entry to the tables before the performance is completed.
The reorganize defragmentation type uses the DBCC INDEX DEFRAG command to reorder the leaf pages of the index in-place. This procedure is similar to a bubble type. Even though the pages are physically reordered, they may not be contiguous within the data file. This dilemma can cause interleaved indexes, which have to be reconstructed to store them in contiguous pages.
Defragmenting an Index Example
Consider a simplified instance of pages after several inserts, updates, and deletes, as revealed in the next figure. The page numbering represents the logical sequence of this pages. On the other hand, the physical string, as shown in the figure from left to right, doesn’t match the logical sequence.
How SQL Defrag Manager Compacts Data
In addition to reordering the leaf pages of this index, SQL Defrag Manager compacts the info in the pages using the original fill variable value specified to your table and then removes any pages that are empty. Think about the following conditions related to this compaction stage:Compaction is totally skipped if the Inhibit Page Locks property is put for the index. There are various calculations built to the compaction stage to stop unnecessary work. For instance, if the first page in the index is vacant and the rest of the pages are complete, SQL Server doesn’t move all the info forwards 1 page. SQL Server compacts pages back to the fill variable value defined for the index. Make sure this value is not set too large. To learn more, visit the SQL Server documentation. In case a lock cannot be obtained on a page during the compaction phase of DBCC INDEX DEFRAG, SQL Server skips that webpage.
Regarding Interleaved Indexes
Interleaving occurs when an index scope, which can be a set of eight index webpages, is not physically contiguous because a scope for another index is intermingled with it. This condition sometimes happens even if there’s no logical fragmentation in the index. Although the pages may be physically and logically arranged, they are not always contiguous. Switching between extents can impact performance as information access is inefficient. To resolve this matter, use SQL Defrag Manager to rebuild the indicators to store them in mini pages and reduce the need to change involving extents.