What is index fragmentation?

Index fragmentation describes how index pages become less optimally organized as data changes. Logical fragmentation refers to pages being out of logical key order, while low page density means pages contain excessive free space.

Fragmentation can matter for some scan-heavy workloads, but it should not be treated as the sole performance metric. Modern maintenance decisions should consider page count, workload, storage type, page density, and measured performance rather than rebuilding every index simply because a percentage crosses a fixed threshold.