Internal index fragmentation is low page density: index pages have free space and the index may require more pages than necessary to return the same data. It is not automatically bad. For write-heavy indexes, deliberately leaving room can reduce costly page splits; for scan-heavy work, low density can mean extra reads. section 11.12 section 11.1
A common mistake is treating every fragmentation reading as a maintenance emergency. A full page receiving an inserted or expanded value may split: SQL Server allocates a page, moves roughly half the rows, and updates page pointers. A split in the middle of an index creates external fragmentation as well as lower page density, adding I/O. Allocation at the end of an index is the preferable kind of split. section 11.1 section 11.12
Leave room where inserts arrive
A nonclustered LastName index built with the default target density has little room for a new value such as Crane. Setting a lower FILLFACTOR when rebuilding can reserve space for later changes. Choose that space as a trade-off, not as a universal target: unused space reduces split risk but makes scans read more pages. section 11.1 section 11.12
Measure page density with avg_page_space_used_in_percent from sys.dm_db_index_physical_stats, alongside external fragmentation. Read the results at each B-tree level, especially the leaf level; averaging density across root, intermediate, and leaf levels can falsely suggest that a freshly rebuilt index remains fragmented. Small upper levels naturally may be sparsely filled. section 11.3 section 11.4
Use maintenance deliberately:
- Rebuild an index when page density must be reset to its FILLFACTOR; rebuilding creates a new B-tree but has a higher resource cost. section 11.3
- Do not reorganize merely to restore free space for a low-FILLFACTOR index: it condenses pages up to FILLFACTOR without reducing density back to that target, so page splits can soon return. section 11.3
- Avoid rebuilding every index indiscriminately; base the decision on fragmentation statistics and focus on leaf-level data. section 11.12
Listing 11.3 Determining index fragmentation
DECLARE @object_id BIGINT
SET @object_id = ( #1
SELECT object_id #1
FROM sys.objects #1
WHERE name = 'Impressions' #1
) ; #1
SELECT #2
OBJECT_NAME(ips.Object_id) AS TableName #2
, i.name AS IndexName #2
, ips.avg_fragmentation_in_percent #2
, ips.avg_page_space_used_in_percent #2
, index_level #2
FROM sys.dm_db_index_physical_stats( #2
DB_ID('Marketing'), #2
@object_id, #2
NULL, #2
NULL, #2
'DETAILED' #2
) ips #2
INNER JOIN sys.indexes i #3
ON ips.index_id = i.index_id #3
AND ips.object_id = i.object_id #3
ORDER BY
ips.object_id
, ips.index_id
, index_level DESC ;
Use the query pattern to inspect page density, external fragmentation, and index level together rather than relying on one aggregate value. section 11.3
