Your Indexes Are Fragmenting Right Now
Every INSERT, UPDATE, and DELETE chips away at index structure. Pages split. Data scatters across disk. Reads that should be sequential become random.
The query slows down. Nobody connects it to the indexes. The team blames the network.
This query tells you which indexes actually need attention — and what to do about them.
What This Query Does
sys.dm_db_index_physical_stats scans the index B-tree and returns fragmentation data.
This runs in LIMITED mode which reads allocation metadata without scanning every page — fast enough to run on production.
The key metrics:
| Column | Meaning |
|---|---|
Fragmentation % | Percentage of pages out of logical order |
Page Fullness % | How full each page is on average |
Row Count | Rows in the index |
Recommended Action | REBUILD (≥30%), REORGANIZE (10–29%), OK |
REORGANIZE is online and can be interrupted.
REBUILD is faster but takes an exclusive lock (unless WITH ONLINE = ON on Enterprise Edition).
This query runs against the current database. Switch context to each database you want to inspect.
Index Fragmentation Script
Loading…
Gareth Winterman