Part of the DBA-Tools Project.
Index fragmentation checks are usually written for one database at a time, which is fine until you inherit an instance with forty databases and no idea which of them has been skipping maintenance. Running a per-database script forty times is nobody’s idea of a health check.
This script does the instance-wide sweep in one go: it walks every online user database, samples fragmentation with the fast LIMITED scan mode, and returns every index at or above 5% fragmentation, worst first. It is the maintenance-planning view, built to answer “where is the fragmentation on this instance” rather than “how bad is this one table”.
Why Index Fragmentation Matters
Fragmentation comes in two forms. Logical fragmentation means the physical page order no longer matches the logical index order, which hurts range scans and read-ahead. Page density problems mean pages are half empty, so the same data occupies more pages and the buffer pool carries the waste. Both accumulate naturally from inserts, updates and deletes; both are worse on indexes with random key patterns.
Whether fragmentation matters as much as it used to is a fair debate (on SSDs the scan penalty is smaller), but page density always matters, heavily fragmented large indexes still waste memory and I/O, and an instance where nothing has rebuilt indexes for a year almost always has other maintenance gaps too. A fragmentation sweep is as much a maintenance audit as a performance check.
- Range scans and reports slow down as page order degrades
- Half-empty pages inflate memory use and backup sizes
- Fragmentation on big tables is a signal that index maintenance is not running
When to Run This Script
- Routine SQL Server health checks
- Taking over an unfamiliar instance, to audit the state of index maintenance
- Before building or tuning index maintenance jobs, to size the problem
- When reports or range-heavy queries degrade gradually over weeks
- After large data loads, archiving runs, or bulk deletes
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Get-IndexFragmentationAcrossDatabases
Category : performance-troubleshooting
Purpose : Check index fragmentation details across all user databases for maintenance planning.
Author : Peter Whyte (https://sqldba.blog/script-check-index-fragmentation-across-all-databases/)
Requires : VIEW DATABASE STATE on each target database
*/
-- SAFE:ReadOnly
-- IMPACT:Medium
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'
USE ' + QUOTENAME(name) + N';
SELECT
DB_NAME() AS database_name,
s.name AS schema_name,
o.name AS table_name,
i.name AS index_name,
ROUND(ips.avg_fragmentation_in_percent, 2) AS avg_fragmentation_percent,
ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''LIMITED'') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE o.is_ms_shipped = 0
AND ips.page_count > 0
AND ips.alloc_unit_type_desc <> ''INTERNAL''
AND ips.avg_fragmentation_in_percent >= 5.0
ORDER BY ips.avg_fragmentation_in_percent DESC;
'
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND database_id > 4;
IF LEN(@sql) > 0
BEGIN
EXEC sys.sp_executesql @sql;
END
ELSE
BEGIN
PRINT 'No online user databases were found for fragmentation review.';
END
It builds one physical-stats query per online user database and executes them in sequence, returning fragmented indexes (5% and up) per database, worst first.
Note the IMPACT:Medium tag: even in LIMITED mode, sys.dm_db_index_physical_stats reads index structures and takes shared locks while it samples. On very large databases run it out of hours the first time, so you know how long it takes on your hardware.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Sweep index fragmentation across all user databases:
.\run.ps1 Get-IndexFragmentationAcrossDatabases
# To run against a remote sql server:
.\run.ps1 Get-IndexFragmentationAcrossDatabases -ServerInstance SQLSERVER01
This script lives in the repo at:
sql/performance/indexes/Get-IndexFragmentationAcrossDatabases.sqlpowershell/wrappers/performance/indexes/Get-IndexFragmentationAcrossDatabases.ps1
Example Output
| database_name | schema_name | table_name | index_name | avg_fragmentation_percent | page_count |
|---|---|---|---|---|---|
| WatchtowerMetrics | dbo | FragmentationWeapon | CX_FragmentationWeapon | 99.12 | 238205 |
| WatchtowerMetrics | dbo | DatabaseGrowthEvents | PK__Database__FE3028636D373B22 | 57.14 | 7 |
| WatchtowerMetrics | dbo | DatabaseGrowthEvents | UX_DatabaseGrowthEvents_GrowthEvent | 33.33 | 6 |
The first row is the real finding here: a 238,205-page clustered index at 99% fragmentation. The other two rows are 6 and 7 page indexes, and despite their scary percentages they are noise, which the next section explains.
Understanding the Results
| Column | What It Means |
|---|---|
database_name |
Which database the index lives in |
schema_name / table_name / index_name |
The index, fully identified |
avg_fragmentation_percent |
Logical fragmentation: how out-of-order the pages are |
page_count |
Index size in 8KB pages, the column that decides whether the percentage matters |
Read page_count before the percentage. Small indexes live in mixed extents and routinely show high fragmentation that is meaningless and often cannot be “fixed” anyway. A common rule: ignore anything under 1,000 pages. In the example above, only the first row is actionable.
5% to 30% on an index that matters: ALTER INDEX ... REORGANIZE is the light-touch option, always online and safe to interrupt.
Over 30% on a large index: ALTER INDEX ... REBUILD creates a fresh copy at the fill factor you configured. Use WITH (ONLINE = ON) on editions that support it if the table needs to stay available.
How to Fix Fragmented Indexes
The one-off fix is a rebuild or reorganize of the specific indexes this sweep surfaces:
-- Light touch, always online:
ALTER INDEX [CX_FragmentationWeapon] ON [dbo].[FragmentationWeapon] REORGANIZE;
-- Full fix, fresh pages at your fill factor:
ALTER INDEX [CX_FragmentationWeapon] ON [dbo].[FragmentationWeapon]
REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);
The real fix is scheduled maintenance, so the sweep stops finding surprises. If this script’s output is long, the instance needs an index maintenance job with sensible thresholds (reorganize 5 to 30%, rebuild above 30%, skip small indexes), plus statistics updates in the same window. Rebuilds are logged operations, so watch transaction log growth on the big ones.
This is the instance-wide sweep, deliberately summary-level. For drilling into one database and generating per-index fix statements, the single-database fragmentation script in the repo is the companion tool.
Related Scripts
You may also find these scripts useful:
- Heaps
- Index Design Issues
- Index Fragmentation
- Index Usage Stats
- Missing Indexes
- Unused Indexes
Frequently Asked Questions
What is a good fragmentation percentage for SQL Server indexes?
Under 5% is clean, 5 to 30% suits a reorganize, and over 30% suits a rebuild. But the page count gates all of it: fragmentation on indexes under roughly 1,000 pages rarely affects anything and can usually be ignored.
Does index fragmentation still matter on SSDs?
Less than it did on spinning disks, because out-of-order reads are cheaper. Low page density still matters everywhere though, since half-empty pages waste buffer pool memory and inflate I/O and backups regardless of storage speed.
How often should I check index fragmentation?
A weekly maintenance job with reorganize/rebuild thresholds handles the routine work. The instance-wide sweep earns its keep on new-to-you servers and health checks, where the question is whether maintenance has been happening at all.
Summary
One run, every database, worst indexes first: this sweep turns “is fragmentation a problem on this instance” from a per-database chore into a single result set. The reading skill is simple but essential, always check the page count before reacting to a percentage.
I use it as an audit tool as much as a tuning tool. A long list of large fragmented indexes says the instance’s maintenance regime needs attention, and that finding usually matters more than any individual index in the output.
Leave a Reply