SQL Index Fragmentation Report
Overview
Percentage with no size behind it is not a ranking. A lookup table with four pages at 98% fragmentation costs nothing to fix and buys nothing. A multi-gigabyte index at 74% is the reason the maintenance window exists.
This report plots size against fragmentation for every index in the database, and ranks by pages out of order, which is the number that combines the two.
What this replaces
The old page was a 3D stacked bar chart of the sixteen most fragmented indexes by percentage, over a nine column grid. Four things were wrong with it:
| Fault | What it meant |
|---|---|
| Ranked on percentage alone | The chart read the top rows off a grid sorted by the fragmentation column, so it filled with tiny lookup tables at 98% while the large index that actually mattered was not on it at all. |
| Re-sorted on every streamed row | The grid re-sorted itself for every single row the background worker reported, and the chart fully repainted every tenth row on top of that. On a database with a few thousand fragmented indexes that is an n log n sort a few thousand times on the UI thread. |
| Double-click wired to Click | Brushing a bar with the mouse put a modal dialog on screen. It recovered which index was clicked by splitting the tooltip on spaces, so any object with a space in its name opened the advisor on the wrong one. |
| Both script buttons scripted everything | REORGANIZE for indexes at 1%, REBUILD for indexes that only needed a reorganize. |
Rows now land in a model and a timer flushes them, the chart carries a row id, and each script button scripts its own band.
Where to find it
| Route | How |
|---|---|
| Server tree | Expand a database → Indexes → Fragmentation |
The page title reads Index Fragmentation for <database name>.
Requirements
- SQL Server compatibility level 90 or higher on the database.
VIEW DATABASE STATE, forsys.dm_db_index_physical_stats.
This report streams. sys.dm_db_index_physical_stats is called one index at a time, which is why results appear progressively rather than all at once, and why the background worker exists at all. On a large database it keeps arriving for a while.
The four verdicts
| Verdict | What it means |
|---|---|
| Healthy | Below the threshold. Nothing to do. |
| Reorganize | Fragmented enough to be worth a reorganize, which is online and resumable. |
| Rebuild | Fragmented enough to be worth a rebuild, which is the heavier operation. |
| Too small | Too few pages for fragmentation to mean anything. |
Too small is its own verdict rather than being hidden. An index below a handful of pages lives inside a mixed extent and its fragmentation figure is noise. Filtering it out silently would leave you wondering where an index went; calling it too small says why it is not actionable.
Reading the map
Every index in the database is plotted, not just the worst sixteen. The two axes are size and fragmentation, and the useful region is the top right: large and fragmented.
The ribbon is a share of the whole database, which is why everything scanned stays in the model even though the grid holds only the worst 10,000 rows. Dropping the tail would make the share a lie.
Click an index to find it in the grid. The chart carries a row id, so this works regardless of what the object is called.
Reading the grid
| Column | What it is |
|---|---|
| Verdict | Healthy, Reorganize, Rebuild or Too small. |
| Index Name | The index. |
| Fragmentation | Average fragmentation in percent. |
| Out of Order | Pages out of order. This is the ranking column. |
| Size | How big the index is. |
| Used | Space actually used. |
| Pages | Page count. |
| Fragments | Fragment count. |
| Table | The table it belongs to. |
| Type | Clustered, nonclustered, and so on. |
| PK / Uniq | Whether it is the primary key, and whether it is unique. |
The grid holds the worst 10,000 indexes. Everything scanned stays in the model behind it.
The script buttons
| Button | What it scripts |
|---|---|
| Script Rebuilds (n) | Only the indexes in the Rebuild band. |
| Script Reorganizes (n) | Only the indexes in the Reorganize band. |
Each button carries its own count, so you know how much you are about to script before you press it. Each scripts its own band only, which the old page did not: it scripted every row in the grid, producing rebuilds for indexes that needed a reorganize and reorganizes for indexes at 1%.
Both produce script for you to read and run elsewhere. Nothing on this page runs against the database.
How to read the report
- Sort by Out of Order, which is the default. Percentage alone will mislead you.
- Ignore the Too small band. Fragmentation on a four page index is noise.
- Work down from the top. The first few rows are usually most of the benefit.
- Check the fill factor before rebuilding repeatedly. An index that returns to the top of this list every week is telling you about its fill factor rather than about maintenance.
- Script the band you want, read the script, and run it in a window where you can watch it.
Common patterns
A handful of huge indexes at moderate fragmentation. The real work. These are what a maintenance window is for.
A long tail of tiny indexes at 90% and above. Noise. They are in the Too small band for a reason.
The same index at the top every week. Fill factor, or an access pattern that fragments it by design. Rebuilding harder will not change it.
Everything healthy on a database nobody maintains. Usually a read-mostly database. Fragmentation needs writes.
Where the data comes from
sys.dm_db_index_physical_stats, called one index at a time, joined to the catalog views for names, types and page counts.
That one-at-a-time call is not a choice: the DMV takes an object and an index, and asking it for everything at once in DETAILED mode on a large database is not something to do to a live server. It is why this report streams.
Nothing is stored. There is no fragmentation history behind this page.
Settings
| Setting | What it does |
|---|---|
IndexFragmentationListView |
Column layout for the grid. |
The fragmentation threshold is configured on the Settings page, and the report names the configured value when nothing clears it.
Messages you may see
Compatibility level too low:
Set your database compatibility mode is set to 90 or higher to use this report.
Nothing above the threshold:
There are no indexes in this database above the threshold. The threshold of … has been configured on the settings page.
Related reports
| Report | Why you would go there |
|---|---|
| Indexing Overview | The whole index picture for the database in one page. |
| Unused Indexes | Whether an index is worth maintaining at all. |
| Most Used Indexes | Whether the ones you are maintaining are the ones being read. |
| Big Clustered Indexes | A wide clustered key makes every nonclustered index bigger and fragment faster. |
| Statistics | The other thing that goes stale as data changes. |
Frequently asked questions
Why is a 98% fragmented index not at the top? Because it is small. The ranking is pages out of order, which is fragmentation multiplied by size, and that is the number that predicts what a rebuild will actually buy you.
What is Too small? An index with too few pages for fragmentation to be meaningful. It lives in a mixed extent and the percentage is noise.
Why does the page keep loading? Because sys.dm_db_index_physical_stats is called one index at a time and the results stream in. On a large database that takes a while by design.
Why do the script buttons have numbers on them? So you know how many statements you are about to generate. They also script only their own band, which the old page did not.
Should I rebuild or reorganize? The verdict column is the report’s opinion based on the configured threshold. Reorganize is online and resumable; rebuild is heavier and more thorough.
An index is back at the top a week after I rebuilt it. Why? Almost always fill factor, or a write pattern that fragments it by design. Rebuilding more often will not fix either.