# Fragmentation and Index Maintenance > [!Note] > Even though SQL Server automatically maintains indexes after DML operations, **fragmentation** inevitably develops over time, degrading **scan performance** and necessitating regular **monitoring** and **maintenance** to preserve query efficiency. Fragmentation occurs when the **logical order** of index pages diverges from their **physical arrangement** on disk, typically after numerous **INSERT**, **UPDATE**, **DELETE**, or **MERGE** operations. While a fragmented index does not impact the precision of **Index Seeks**, it can substantially slow down **full** and **range scans** by forcing the engine to jump between noncontiguous pages. > [!tip] > Use the dynamic management function **sys.dm_db_index_physical_stats** to measure both **internal** and **external fragmentation** levels and identify indexes that exceed your fragmentation thresholds. To keep fragmentation in check, SQL Server’s optimizer considers only the **number of pages** during plan selection, not their physical order—so a fragmented index may still be chosen, but its runtime I/O cost will be higher. Regular **maintenance** through **REORGANIZE** or **REBUILD** operations can restore page contiguity: **REORGANIZE** performs an online, low-impact defragmentation, whereas **REBUILD** reconstructs the entire index (offline or online) to eliminate fragmentation completely. > [!warning] > Choose maintenance actions based on fragmentation severity and system workload: excessive online rebuilds can consume tempdb and lock resources, while too infrequent reorganizations allow fragmentation to accumulate. --- ## ## References - Korotkevitch, D. (2022). _SQL Server advanced troubleshooting and performance tuning: Best practices and techniques_. O’Reilly Media. - Nevarez, B. (2022). _SQL Server query tuning and optimization: Optimize Microsoft SQL Server 2022 queries and applications_. Packt Publishing.