Index fragmentation is when the logical order of an index's pages no longer matches their physical order, or pages are under-filled — making SQL Server read more pages than necessary for the same data.
You address it by reorganizing (lighter, always online, for moderate fragmentation) or rebuilding (heavier, best result, for high fragmentation). The classic thresholds are roughly 10–30% reorganize, above 30% rebuild — but treat them as starting points, not laws.
Index maintenance is one of the most over-done tasks in SQL Server — and one of the most misunderstood. This guide explains what fragmentation actually costs, how to choose reorganize vs. rebuild, and why "rebuild everything nightly" is usually the wrong instinct.
What fragmentation is
There are two kinds, and they are not equally important:
- Logical (external) fragmentation — the index's pages are out of physical order relative to their key order, so range scans jump around the disk.
- Internal fragmentation (low page density) — pages are partly empty, so the same rows occupy more pages. This is the one that still reliably costs you, because more pages must be read into memory.
Why it matters — and the honest caveat
Fragmentation increases the number of pages SQL Server touches, which shows up as higher logical reads on large scans. That said, on modern SSD and cloud storage the penalty for out-of-order pages is far smaller than it was on spinning disks. The part that still matters most is page density — under-filled pages waste memory regardless of storage speed.
Rebuild vs. reorganize
| REORGANIZE | REBUILD | |
|---|---|---|
| Resource cost | Lower; often minutes | Higher; can take hours |
| Online? | Always online | Online edition takes a brief SCH-M lock |
| Updates statistics? | No | Yes (full-scan equivalent) |
| Best for | Moderate fragmentation (~10–30%) | High fragmentation (>30%) |
| Can stop midway? | Yes — resumable | Rolls back unless resumable rebuild is used |
Per Microsoft and Brent Ozar, reorganize should usually be the default unless there is a specific reason to rebuild — it is online and far cheaper.
The thresholds are guidelines, not laws
The 10–30%/>30% rule is a useful default, but Microsoft's own guidance is that the benefit of index maintenance should be determined empirically for each workload and weighed against its resource cost. Ignore fragmentation on tiny indexes, and don't rebuild large indexes nightly out of habit — the maintenance can cost more than the fragmentation.
Where this fits (and what SprocOptimizer does not do)
To be clear about scope: index fragmentation maintenance is a routine DBA housekeeping task, distinct from query and index-design optimization. SprocOptimizer focuses on the latter — analyzing a stored procedure's plans and recommending query rewrites and index changes (such as a covering index), validated for identical results. It does not run your rebuild/reorganize maintenance jobs; that remains part of your normal maintenance plan and complements the query-side work.
Frequently asked questions
Index fragmentation is when the logical order of an index's pages no longer matches their physical order, or pages are under-filled (low page density). Both force SQL Server to read more pages than necessary to retrieve the same rows, which can increase I/O and logical reads, especially for large range scans.
Reorganize is the lighter, always-online option that defragments the leaf level without dropping the index; rebuild recreates the index entirely, costs more resources and can take much longer, but gives the best result and also updates statistics with a full scan. Microsoft's long-standing rule of thumb is to reorganize when fragmentation is roughly 10–30% and rebuild above 30% — but treat those as starting points, not laws.
The classic Microsoft guidance is to rebuild when fragmentation exceeds about 30% and reorganize between roughly 10% and 30%, and to ignore fragmentation on very small indexes. These thresholds are guidelines; the real benefit should be determined empirically for your workload and weighed against the resource cost of the maintenance.
It matters less than it once did. On fast SSD and cloud storage, the penalty for out-of-order pages is smaller than on spinning disks. However, low page density (internal fragmentation) still means more pages must be read into memory for the same data, so very high fragmentation and under-filled pages are still worth addressing — just don't rebuild constantly out of habit.
Primary sources & further reading
- Microsoft Learn — Reorganize and rebuild indexes.
- Brent Ozar — Rebuild or Reorganize: Configure SQL Server Index Maintenance.
- MSSQLTips — SQL Index Rebuild vs Reorganize Comparison.
Optimize the queries, not just the maintenance plan
SprocOptimizer tackles the query and index-design side of performance — analyzing plans and validating rewrites — on-premises, with no row-level data leaving your network.
Request a Demo Read the Optimization Guide