To understand how SQL Server defragments an index, consider a simplified example of pages after many insertions, updates, and deletes, as shown in the following figure.
The numbering of the pages represents the logical sequence of the pages.
However, the physical sequence, as shown in the figure from left to right, does not correspond to the logical sequence.
The following figure illustrates several passes during the defragmentation and reorganization process, which causes the physical pages to be reordered by changing the first logical page with the first physical page, then the second logical page swapped with the second physical page, and so on. after.
During the first pass, SQL Server find the first physical page (4) and the first logical page (1), then exchange these pages in a discrete transaction.
On the second pass, SQL Server swap the next physical page (7) with the following logical page (2).
On the third pass, SQL Server swap the next physical page (4) with the following logical page (3).
On the fourth pass, SQL Server swap the next physical page (5) with the following logical page (4).
Sorting is now complete because all physical pages match their logical positions.
Leave a comment