An example to better understand :
Imagine that there are 3 pages of data for a table with a cluster index. A new row with a primary key of "5" must be inserted, and since it is a clustered index, the new row is inserted in order. Because the target page is sufficiently complete that the new row can not be inserted, SQL Server divides the page into approximately two and inserts the new data on the new page, as shown in the figure above.
Now, the logical order of the index does not correspond to the physical order, and the index has become fragmented.
SELECT
I.index_id, I.
name
,
DM.avg_fragmentation_in_percent
FROM
Sys.dm_db_index_physical_stats(db_id(),
NULL
DEFAULT
) DM
JOIN
sys.Indexes I
ON
I.object_id=DM.object_id
AND
I.Index_id=DM.index_id
Result :
USE AdventureWorks2012;
GO
-- Reorganize all indexes on the HumanResources.Employee table.
ALTER
INDEX
ALL
HumanResources.Employee
REORGANIZE ;
PK_Employee_BusinessEntityID
REBUILD;
To understand how SQL Server defragments an index, consider a simplified example of pages after many insertions, updates, and deletions, as shown in the following figure :
Page numbering represents the logical sequence of 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, causing re-scheduling of physical pages by changing the first logical page with the first physical page and swapped with the second physical page and so on.
During the first pass, SQL Server finds the first physical page (4) and the first logical page (1), and then exchanges these pages in a discrete transaction.
On the second pass, SQL Server exchanges the next physical page (7) with next logical page (2).
On the third pass, SQL Server exchanges the next physical page (4) with the next logical page (3).
On the fourth pass, SQL Server exchanges the next physical page (5) with the next logical page (4).
Sorting is now complete because all physical pages correspond to their logical positions.