SQL Server index fragmentation is a page-ordering mismatch. The physical order of pages on disk stops matching the logical key order of the index, and...
SQL Server index fragmentation is a page-ordering mismatch. The physical order of pages on disk stops matching the logical key order of the index, and the gap widens every time an insert, update, or delete forces a page split. A fragmented index can push SQL Server into more, smaller I/O reads during a range scan. The fragmentation percentage is one input, and acting on it in isolation burns maintenance windows. This guide covers the mechanics, the measurement, the rebuild-versus-reorganize decision, and where fragmentation fits in a full index health check.
An index has a logical order defined by its key columns, which is the sequence SQL Server follows when it walks the index. It also has a physical order, which is the sequence of 8 KB pages as they sit in the data file. Fragmentation is the distance between those two orders. When a page is full and a new row belongs in the middle of it, SQL Server splits that page in two and allocates the new page wherever free space happens to exist, so the physical sequence drifts out of step with the key sequence. Sustained insert, update, and delete activity keeps that drift growing. It’s a layout and performance condition affecting how efficiently SQL Server reads a range, and it carries no implication that data is missing or incorrect.
sys.dm_db_index_physical_stats is the dynamic management function that reports SQL Server index fragmentation per index. On its own it returns object and index IDs, so joining it to the catalog views is what produces a report DBAs can parse directly.
SELECT s.name AS schema_name,
t.name AS table_name,
i.name AS index_name,
ps.index_type_desc,
ps.avg_fragmentation_in_percent,
ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘LIMITED’) AS ps
JOIN sys.tables AS t ON ps.object_id = t.object_id
JOIN sys.schemas AS s ON t.schema_id = s.schema_id
JOIN sys.indexes AS i ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
WHERE ps.index_id > 0
ORDER BY ps.avg_fragmentation_in_percent DESC;
Two columns carry the decision. avg_fragmentation_in_percent expresses the logical-to-physical mismatch as a percentage, and it is the number most maintenance thresholds are written against. page_count tells you whether the index is large enough for the percentage to mean anything. Microsoft’s guidance treats indexes under roughly 1,000 pages as generally not worth acting on, because a small index is often resident in memory and the reordering work rarely pays for itself. The LIMITED scan mode reads only the leaf level’s parent pages and is the least expensive option for routine reporting.
This works for one database. Across forty, it becomes a permanent maintenance project. If the T-SQL upkeep across every database is work you would rather skip, IDERA’s free SQL Fragmentation Analyzer produces the same per-index fragmentation view for a single instance and returns the results directly.
Microsoft’s documented baseline recommends REORGANIZE up to approximately 30% fragmentation and REBUILD above it, with 5% as the floor where any action becomes worthwhile. Treat those numbers as the vendor’s starting point, since your own write patterns and window length will determine your actual thresholds. How REBUILD and REORGANIZE differ mechanically decides what your maintenance window can absorb.
| Operation type | ALTER INDEX REBUILD | ALTER INDEX REORGANIZE |
| Method | Drops and recreates the index | Reorders existing leaf pages in place |
| Concurrency | Offline by default; online in supported editions | Always online, lower impact |
| Statistics | Updated with a full scan | Left untouched |
| Fill factor | Applies the configured fill factor | Compacts using the existing fill factor |
| Free space | Needs roughly the size of the index again | Works in place, minimal additional space |
| Interruption | Restarts from the beginning | Can be stopped and resumed |
Setting a fill factor below 100 leaves free space on each leaf page so future inserts have somewhere to go, which reduces page splits. That mechanism is why fill factor belongs in the REBUILD/REORGANIZE decision. The cost is lower page density and a higher total page count, meaning more pages to read for the same rows and more buffer pool consumed. Because only REBUILD applies the configured fill factor, changing that setting on a high-churn index is a reason to choose REBUILD even when the percentage would point toward REORGANIZE.
The thresholds themselves are contested. Brent Ozar’s team argues the guidance predates SSD storage and says nothing about whether the data is already memory-resident, where the sequential-read advantage of a defragmented index largely disappears. In their view, fragmentation on its own rarely causes a provable performance problem without a page-density or I/O-pattern component, and the statistics update a rebuild performs is often the real source of observed improvement. This is a single-source practitioner position, and it sits alongside Microsoft’s documented baseline as an open question in the field. Both are worth reading in full before you write a threshold into a maintenance job.
A fragmentation percentage says nothing about whether the index deserves to exist. It measures page order on an object without reference to how that object is queried, how often, or whether the optimizer wanted a different index entirely. A DBA working from a SQL Server index fragmentation report alone has an ordered list of maintenance work with no way to tell which entries earn the window they will consume.
Two more DMV signals close that gap. Fragmented, Missing, and Unused together form one triage pass covering what is disordered, what is absent, and what is consuming resources for no return.
| Signal | DMV source | What it shows | Action it drives |
| Fragmented | sys.dm_db_index_physical_stats | Page order and page count per existing index | Rebuild or reorganize, sized to the window |
| Missing | sys.dm_db_missing_index_details and companions | Indexes the optimizer would have used, derived from compiled plans, with no cross-query context | Create the index the workload is asking for |
| Unused | sys.dm_db_index_usage_stats | Seeks, scans, lookups, and updates per index since the last service restart | Drop the index and reclaim the write overhead |
The payoff is a shorter, justified work list. Running all three checks together keeps you from rebuilding unused indexes and surfaces the creates that fragmentation reporting misses.
SQL Diagnostic Manager’s Index Analysis feature gives DBAs this combined view, surfacing missing, fragmented, and unused indexes together with optimization recommendations across every instance it monitors, from SQL Server 2012 through 2022 and into Azure SQL Database and Amazon RDS. For a fleet, that consolidation turns a per-database script run into an environment-wide triage list, and the historical trending behind it shows whether a given index refragments fast enough to justify a recurring job.
An index sitting at 70% fragmentation with near-zero seeks and scans in sys.dm_db_index_usage_stats belongs on the drop list. Rebuilding it consumes window time, transaction log, and CPU to reorder pages no query reads. The larger cost is ongoing, because every insert, update, and delete against the base table maintains that index too, so an unused index taxes write throughput indefinitely and grows the backup footprint along with it. Confirm the usage numbers span a full business cycle before dropping, since counters reset on service restart and a quarter-end report may be the only thing that touches the index.
Missing-index output points at indexes the optimizer would have chosen if they existed, which is a different question from how the current indexes are laid out. When a missing-index recommendation appears against a table already carrying several fragmented indexes, the higher-value work is often creating the index the optimizer asked for, since a freshly built index arrives with clean page order and current statistics. Reading missing-index findings next to fragmentation findings also narrows the defragmentation list, because it shows which tables are under real query pressure. Effort concentrates on indexes confirmed to matter, and the long tail of mildly fragmented objects nobody queries drops off the list.
Manual DMV scripts answer the SQL Server index fragmentation question for one database at a time, which stops working across an estate of instances. Scope is what separates the three options a DBA has here.
Start a free trial of SQL Diagnostic Manager and run Index Analysis against the instances you maintain before the next window opens.
What distinguishes index fragmentation from data corruption?
Fragmentation is a page-ordering condition that affects how efficiently SQL Server reads a range of rows. Corruption means pages are damaged or unreadable and is diagnosed with DBCC CHECKDB. A fragmented index returns complete, correct results, and the reads behind them are less efficient.
Does index fragmentation still matter on all-flash or memory-resident systems?
Microsoft’s documented thresholds still apply as a baseline. Brent Ozar’s team argues that the performance impact shrinks substantially when storage is SSD-based or the data is largely resident in the buffer pool, since the sequential-read advantage is what fragmentation costs you. The write-side overhead of maintaining an extra index stays the same either way. Weigh both positions against your own I/O and wait statistics.
How often should you check for index fragmentation?
It depends on write volume and window availability. High-churn OLTP tables justify weekly reporting, while mostly-read warehouses tolerate monthly. Scheduling the check with a maintenance solution like Ola Hallengren’s keeps the cadence consistent.
What happens if an index rebuild runs out of free space?
REBUILD creates a new copy of the index before dropping the original, so it needs roughly the size of the index again in free space, plus log space. The operation rolls back if that space is unavailable. REORGANIZE works in place and is the lower-space-impact alternative when the data file is tight.
Contact our sales team to get a personalized demo of our database management software for SQL Server!
