We see the same story on client server after client server: a rebuild job that used to finish before the 6 a.m. backup now finishes closer to nine, and nobody touched the maintenance plan to cause it. SQL Server index fragmentation is the name that gets attached to it once someone notices. The database grew, the list of indexes waiting on attention grew with it, and the maintenance window did not.
Why is SQL Server index fragmentation percentage the wrong number to sort by? SQL Server index fragmentation percentage alone is misleading because it ignores size: a four-page lookup table can show 98 percent fragmented while costing nothing to fix. The number that predicts what a rebuild will actually buy back is pages out of order, which multiplies fragmentation by index size and ranks the indexes a maintenance window is actually for first.
Database Health Monitor's Index Fragmentation report was built for exactly that moment, when the list of fragmented indexes has grown past what one maintenance window can fix and somebody has to decide which ones actually matter. Most people reach for the same number first, and it is the wrong one.
In this post
- How SQL Server Index Fragmentation Gets Sorted Wrong
- Pages Out of Order: The Number That Accounts for Size
- Inside the Index Fragmentation Report
- Reading the Map: Size Against Fragmentation
- The Grid, the Verdicts, and the Script Buttons
- Reorganize, Rebuild, or Leave It Alone
- When the Same Index Keeps Coming Back
How SQL Server Index Fragmentation Gets Sorted Wrong
The instinct is reasonable enough. Sort every index by fragmentation percent, descending, and start rebuilding from the top. The trouble is that a four-page lookup table can sit at 98 percent and cost nothing to fix, while a multi-gigabyte index at 74 percent, further down that same list, is the one the maintenance window actually exists for. Percentage on its own has no size behind it. It cannot tell the difference between an index nobody will ever notice and the one everybody is waiting on.
Query sys.dm_db_index_physical_stats directly and the same trap is waiting. The view hands back a fragmentation percentage for every index on a database at compatibility level 90 or higher, and nothing in that output ranks importance. Sort the result set by that one column and you get the same list back: small, noisy, and expensive to work through for very little benefit.
Pages Out of Order: The Number That Accounts for Size
Multiply fragmentation percent by how many pages an index has, and the result behaves the way intuition wants percentage to behave in the first place. A four-page index at 98 percent barely moves that number, because there is almost nothing there to be out of order. A large index at a far lower percentage moves it a great deal, because there is a great deal of it sitting out of order. That combined figure is pages out of order, and it is the column worth sorting by.
It is also the default sort in the Index Fragmentation report. Turn it on once and the four-page tables settle toward the bottom on their own. Nobody has to remember to ignore them, and nobody has to eyeball a percentage column wondering whether that 98 belongs to something that matters.
Inside the Index Fragmentation Report
Database Health Monitor plots every index in the database on one chart, not just the worst few. Two axes, size against fragmentation, with a ranking column underneath doing the actual work: pages out of order. It replaced an older page built as a 3D stacked bar chart covering only the sixteen worst indexes by percentage, and the trouble with that page went well past the chart type.
Every row streaming in from the background worker triggered a fresh sort of the grid behind it, and the chart fully repainted right along with it. On a database carrying a few thousand fragmented indexes, that turned into a few thousand sorts running on the same thread the interface needed to stay responsive. Double-clicking a bar in that chart opened a dialog for whatever index the tooltip text seemed to match, found by splitting the label on spaces, so an object with a space in its name could send you to the wrong advisor entirely. Both script buttons scripted every row on screen, generating rebuild statements for indexes that only needed a reorganize and reorganize statements for indexes sitting at one percent.
None of that survives in the current version. Rows land in a model and a timer flushes them to screen instead of repainting on every row, each point on the chart carries its own row id rather than a name to parse, and each script button scripts only its own band.
Index Fragmentation is one of the reports in Database Health Monitor. It runs against your own servers, and it takes about a minute to have this same screen open on one of them.
Reading the Map: Size Against Fragmentation
Every index in the database lands on this chart, not just a worst-sixteen list. One axis is size, the other is fragmentation, and the region worth caring about is the top right corner: large and fragmented at once. A tiny index at 98 percent sits over at the left edge, exactly where it belongs, barely visible against everything else on screen.
A ribbon across the chart shows what share of the whole database each part represents, and that only holds up because everything scanned stays in the model behind the chart even though the grid beneath it holds only the worst 10,000 rows. Drop the tail and that share turns into a number nobody should trust. Click a point and it highlights in the grid below, regardless of what the index is named, because the chart is tracking a row id rather than parsing a label off the screen.
The Grid, the Verdicts, and the Script Buttons
Below the chart sits a grid, sorted by pages out of order by default, holding the worst 10,000 indexes while everything else scanned is still counted behind it. Each row carries a verdict rather than a bare percentage.
| Verdict | What it means |
|---|---|
| Healthy | Below the configured threshold. Nothing to do. |
| Reorganize | Fragmented enough to be worth fixing online, without taking the index offline. |
| Rebuild | Fragmented enough to need the heavier operation. |
| Too small | Too few pages for a fragmentation figure to mean anything. |
Too small gets its own verdict instead of being quietly filtered out, and that is deliberate. An index with only a handful of pages lives inside a mixed extent, and its fragmentation figure is noise rather than signal. Hide it and you would be left wondering where the index went; calling it too small tells you why it never needed your attention in the first place.
Two buttons sit above the grid: one scripts the rebuild band, one scripts the reorganize band, and each carries a count so you know how many statements you are about to generate before you press anything. Neither runs anything against the database. Both hand you a script to read and run somewhere you can watch it.
Reorganize, Rebuild, or Leave It Alone
Reorganize is online and resumable, which makes it the cheaper option whenever it is enough. Rebuild is heavier and more thorough, and the verdict column already made that call based on the threshold configured on the settings page. Work down from the top of the list rather than across all of it. The first handful of rows is usually most of the benefit, and everything in the Too small band is not worth the time it takes to read.
- A handful of large indexes at moderate fragmentation is the real work, and usually the reason the maintenance window exists at all
- A long tail of tiny indexes sitting at 90 percent and above is noise, and it is exactly why the Too small band exists
- The same index landing at the top of the list every week is a fill factor problem, or a write pattern that fragments it by design, not a rebuild problem
- A database that comes back completely healthy every time is usually a read-mostly database, since fragmentation needs writes to happen at all
When the Same Index Keeps Coming Back
An index that returns to the top of this list a week after being rebuilt is not asking for a harder rebuild. It is telling you something about its fill factor, or about a write pattern that fragments it by design, and rebuilding it again on the same schedule will not change either one. Check the fill factor before scheduling another pass at it.
Fragmentation by itself does not prove a query is slow, only that an index is not stored the way it should be. If the goal is to see whether it is actually costing time at the query level, pair this with SQL Server Waits by Query: Find What's Actually Slow, which shows which queries are burning time and on what kind of wait, rather than which indexes look untidy.
The Index Fragmentation report is one piece of a larger index picture. Indexing Overview gives the whole picture for a database in one page. Unused Indexes asks whether an index is worth maintaining at all before a maintenance window gets spent fixing it. Big Clustered Indexes explains why a wide clustered key makes every nonclustered index on the table bigger and faster to fragment. None of that changes what this particular report is for: telling you which index, out of everything in the database, actually earns the time it takes to fix.
For the full column-by-column reference, including every setting behind the threshold and every message the report can show, see the Index Fragmentation documentation.
What to check on your own server
- Check that compatibility level is 90 or higher on the database before pulling fragmentation stats
- Query sys.dm_db_index_physical_stats and multiply fragmentation percent by page count to rank indexes by pages out of order
- Skip any index with only a handful of pages, since fragmentation on it is noise
- Reorganize the moderately fragmented indexes online and save rebuild for the heavier cases
- Check fill factor on any index that lands back at the top a week after being rebuilt
Try Database Health Monitor Today
The Index Fragmentation report stops a maintenance window being spent rebuilding a four-page lookup table while the index everyone is waiting on sits untouched. Database Health Monitor shows it on every instance you connect, in the time it takes to open the report.
Download Database Health Monitor and run the Index Fragmentation report against your own server. There is nothing to configure first, and you will know inside a few minutes whether it tells you something you did not already know.