The process of dropping and recreating a SQL Server index to eliminate fragmentation, reset fill factor, and reclaim wasted space, improving query performance and I/O efficiency.
- Index fragmentation occurs when data modifications (INSERT, UPDATE, DELETE) cause index pages to become out-of-order or sparsely populated: This degrades sequential scan performance.
- SQL Server offers two remediation options: Rebuild drops and recreates the index, eliminating all fragmentation, while Reorganize defragments the index in place, making it less disruptive but also less thorough.
- Online index rebuild (Enterprise Edition) allows the index to remain available for reads and writes during the rebuild operation: This capability is critical for 24/7 production environments.
- General guidance: Reorganize indexes with 5–30% fragmentation, rebuild indexes with more than 30% fragmentation, and ignore fragmentation on very small indexes containing fewer than 1,000 pages.
- Index rebuilds update statistics, while Reorganize does not: This difference should be considered when planning index and statistics maintenance.
- Relevant Idera tools: SQL Defrag Manager automates intelligent index maintenance by analyzing fragmentation and selecting rebuild or reorganize operations based on configurable thresholds.
- Related terms: Fragmentation, Fill Factor, Bloat, Page Split, Index Maintenance, SQL Defrag Manager.
