Page History

...

When page splits occur to make room for information added to a page, there may be excessive free space left on the pages. This extra space can cause SQL Server to read more pages than necessary to perform certain tasks. This issue is known as page density fragmentation.

What is an example of fragmentation?

Imagine there are two data pages for a table with a clustered index. The data is ordered and the pages are full as shown in the following figure. A new row with a primary key of "5" needs to be inserted, and since it is a clustered index, the new row is inserted in order. Because the target page is full enough that the new row does not fit, SQL Server splits the page roughly in half and inserts the new data on the new page, as shown in the following figure. Now, the logical order of the index does not match the physical order, and the index has become fragmented.

Why is defragmenting indexes and tables important?

SQL defrag manager Defrag Manager defragments the leaf level of an index so the physical order of the pages matches the left-to-right logical order of the leaf pages. The leaf pages of a clustered index contain the table data. This process improves index scanning performance and all data retrieval activities. 

How does

...

SQL Defrag Manager defragment indexes?

Depending on the policy settings you select, SQL defrag manager Defrag Manager defragments tables and indexes in one of the following ways: 

Rebuild

The rebuild defragmentation type uses the DBCC DBREINDEX command to rebuild the indexes on the tables. The rebuild operation creates new, contiguous pages. SQL Server 2005/2008 allows the option to Rebuild Online, which allows access to the tables before the operation is finished. However, choosing to rebuild online requires more resources (disk space, CPU, memory), and may slow performance. 

...

Reorganizr

The reorganize defragmentation type uses the DBCC INDEXDEFRAG command to reorder the leaf pages of the index in-place. This process is similar to a bubble sort. Although the pages are physically reordered, they may not be contiguous within the data file.

...

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 to rebuild the indexes to store them in contiguous pages and reduce the need to switch between extents.

Need more help? Search the Idera Customer Support Portal

...