# Fragmentation and Index Maintenance Thresholds > [!Note] > **Index fragmentation** degrades scan performance over time, so monitoring with **sys.dm_db_index_physical_stats** and applying **reorganize** or **rebuild** operations at defined thresholds preserves query efficiency. Even though SQL Server automatically updates index structures after DML operations, logical fragmentation—where the physical ordering of pages drifts apart—accumulates. While **Index Seeks** remain accurate on fragmented indexes, **full** and **range scans** suffer from increased I/O as the engine jumps between noncontiguous pages. Regular monitoring and maintenance ensure that fragmentation stays within acceptable bounds and that query plans do not incur unnecessary cost due to scattered pages. > [!tip] > Run `sys.dm_db_index_physical_stats` periodically to capture fragmentation levels and identify candidates for defragmentation. Below is a concise tabular summary of action thresholds and corresponding maintenance operations: |**Fragmentation Level**|**Recommended Action**|**Command**|**Characteristics**| |---|---|---|---| |**0–10%**|No action required|N/A|Performance impact is negligible; overhead not justified.| |**10–30%**|Reorganize index|`ALTER INDEX … REORGANIZE`|Online, low-impact defragmentation of leaf level without rebuilding the entire B-Tree.| |**> 30%**|Rebuild index|`ALTER INDEX … REBUILD [WITH (ONLINE = ON)]`|Full reconstruction of all pages; removes fragmentation and can update fillfactor.| > [!warning] > Frequent online rebuilds consume **tempdb** and can incur locks; balance their use against system workload and available resources. --- ## ## 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.