Page History
As data is modified in a database, the database and its indexes become fragmented. As indexes become fragmented, ordered data retrieval becomes less efficient and reduces database performance.
Understanding the different types of fragmentation
...
There are several types of fragmentation that can occur and impact SQL Server performance and space usage. Note that logical order and page density issues exist on tables and indexes within SQL Server. These issues cannot be resolved by operating system level defragmentation tools because the fragmentation exists within the files, rather than at the file level itself.
...
This issue, also known as internal fragmentation, occurs as pages split , causing suboptimal use of the capacity of each page. There may be excessive free space left on the pages. SQL Server may need to read more pages than necessary to satisfy a query or perform other activities.
...
to
...
Fragmentation within SQL Server occurs in much the same way. When data is inserted, deleted, and modified over time in a table, an index can cause the pages to be out of order. The next logical page may not be the same as the next physical page. This issue is known as logical order fragmentation.
...
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.
...
SQL
...
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.
Fragmentation examples
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.
...