What is index fragmentation and how does it impact performance?

Index fragmentation occurs when index pages become disordered, reducing query efficiency.

Index fragmentation happens when data is inserted, updated, or deleted, causing index pages to split or become non-contiguous. This leads to additional I/O operations because the database engine must traverse more pages. There are two types: internal fragmentation (wasted space inside pages) and external fragmentation (pages scattered on disk). High fragmentation increases query latency and reduces cache efficiency.