SQL Server Index Fragmentation: dm_db_index_physical_stats

calendar Apr 3, 2026 / · 10 min read · sql server index maintenance sql scripts sys.dm_db_index_physical_stats sys.indexes sys.objects fragmentation database administration performance maintenance scripts indexes rebuild reorganize  ·
Share on: linkedin copy

This T-SQL query lists the rowstore indexes in the current database whose leaf level is fragmented and large enough to be worth maintaining, and returns a ready-to-review ALTER INDEX statement for each one. It reads sys.dm_db_index_physical_stats in SAMPLED mode and filters on avg_fragmentation_in_percent and page_count.

Purpose and Overview

Index maintenance planning starts with a list: which indexes are fragmented, how big they are, and whether a reorganize or a rebuild fits each one. Running a blanket rebuild across every index on a schedule answers none of those questions and burns log space, I/O and CPU on indexes that were fine. A measured list lets the maintenance window go where it pays.

Two different problems hide behind the word fragmentation. Logical fragmentation is the share of leaf pages that sit out of order, so a range scan has to jump around the file instead of reading forward. Page density is how full the pages are: a page that split in half leaves two pages at roughly 50 percent, and low density means more pages to read, cache and back up for the same rows. avg_fragmentation_in_percent measures the first, avg_page_space_used_in_percent the second, and the script returns both.

Fragmentation by itself is not a reason to act. Low page density adds I/O to scans and seeks and can add B-tree levels, but many gains seen after a rebuild come from the fresh statistics rather than from the lower fragmentation. The thresholds in the script are starting points for deciding what to look at, not rules the engine enforces.

Code Breakdown

The script runs in one database. The three variables at the top hold the cutoffs, and the output carries the suggested action and the statement to run.

 1USE [YourDatabase];
 2GO
 3
 4DECLARE @MinFragPct     FLOAT = 10;    /* ignore indexes below this logical fragmentation */
 5DECLARE @RebuildFragPct FLOAT = 30;    /* at or above this, suggest REBUILD; below it, REORGANIZE */
 6DECLARE @MinPages       BIGINT = 1000; /* ignore leaf levels smaller than this */
 7
 8SELECT
 9    DB_NAME(ips.database_id)                      AS database_name,
10    OBJECT_SCHEMA_NAME(ips.object_id)             AS schema_name,
11    OBJECT_NAME(ips.object_id)                    AS table_name,
12    i.name                                        AS index_name,
13    ips.partition_number,
14    ips.index_type_desc,
15    ips.avg_fragmentation_in_percent              AS external_fragmentation_pct,
16    ips.avg_page_space_used_in_percent            AS page_density_pct,
17    ips.page_count,
18    CASE WHEN ips.avg_fragmentation_in_percent >= @RebuildFragPct
19         THEN 'REBUILD' ELSE 'REORGANIZE' END     AS suggested_action,
20    'ALTER INDEX ' + QUOTENAME(i.name)
21        + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(ips.object_id))
22        + '.' + QUOTENAME(OBJECT_NAME(ips.object_id))
23        + CASE WHEN ips.avg_fragmentation_in_percent >= @RebuildFragPct
24               THEN ' REBUILD;' ELSE ' REORGANIZE;' END AS maintenance_command
25FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
26INNER JOIN sys.indexes AS i
27    ON  i.object_id = ips.object_id
28    AND i.index_id  = ips.index_id
29INNER JOIN sys.objects AS o
30    ON  o.object_id = ips.object_id
31WHERE ips.index_level = 0
32  AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
33  AND i.type IN (1, 2)
34  AND i.is_disabled = 0
35  AND o.is_ms_shipped = 0
36  AND ips.avg_fragmentation_in_percent > @MinFragPct
37  AND ips.page_count > @MinPages
38ORDER BY ips.avg_fragmentation_in_percent DESC;

The Function and Its Five Arguments

sys.dm_db_index_physical_stats is a table-valued function that takes a database ID, an object ID, an index ID, a partition number and a mode. NULL in the object, index and partition positions means all of them. Passing DB_ID() limits the scan to the current database; a NULL database ID would cover every database on the instance, and then the other three arguments must also be NULL.

Be careful with OBJECT_ID() as an argument. It is evaluated in the context of the current database, and an object name that does not resolve returns NULL, which the function reads as "all objects". A typo in a table name therefore scans the whole database instead of failing.

Scan Modes

The mode sets how much of each index is read:

  • LIMITED is the default and the fastest. For an index it reads only the parent-level pages above the leaf. It returns avg_fragmentation_in_percent but leaves avg_page_space_used_in_percent and the record-size columns NULL.
  • SAMPLED returns statistics from a 1 percent sample of the pages, so the numbers are approximate. When the index or heap has fewer than 10,000 pages, the engine uses DETAILED instead.
  • DETAILED reads every page and returns every column.

The script uses SAMPLED because the page-density column is wanted and a full read of every leaf page is not. Because indexes under 10,000 pages fall back to a full scan, the cheap sample only applies to the large indexes, which are the ones where a full scan would hurt.

Leaf Level and Row Filters

Non-leaf levels are processed only in DETAILED mode, and the SAMPLED fallback for small indexes means they can show up anyway, each with its own page_count. ips.index_level = 0 keeps only the leaf level so one index produces one row per partition. alloc_unit_type_desc = 'IN_ROW_DATA' drops the LOB and row-overflow allocation units, which report 0 for avg_fragmentation_in_percent anyway.

i.type IN (1, 2) limits the result to clustered and nonclustered rowstore indexes. Those are the types that ALTER INDEX ... REORGANIZE and REBUILD treat the way the output suggests. Columnstore indexes measure fragmentation as the share of deleted rows in a row group, which sys.dm_db_column_store_row_group_physical_stats reports, and heaps need their own handling (see Practical Tips). i.is_disabled = 0 removes disabled indexes, and o.is_ms_shipped = 0 removes objects created by SQL Server itself.

Joining sys.indexes

The function returns an index_id but not an index name, so the join to sys.indexes on object_id and index_id supplies the name and the type column. A heap has a row in sys.indexes with index_id = 0 and a NULL name, so the join does not remove heaps; the i.type IN (1, 2) condition does.

Thresholds and the Generated Command

The CASE expression maps fragmentation at or above 30 percent to REBUILD and anything between 10 and 30 percent to REORGANIZE. The 10 and 30 percent cutoffs are conventional starting values, and the page-count floor keeps small indexes out of the list. The maintenance procedure IndexOptimize defaults to 5 percent, 30 percent and 1,000 pages. QUOTENAME protects the generated statement against names with spaces or reserved words. The partition_number column is in the output because a partitioned index returns one row per partition, and ALTER INDEX ... REBUILD PARTITION = n can rebuild just the one that is fragmented instead of the whole index.

Key Benefits and Use Cases

  • One list for the whole database. A single query ranks every rowstore index by logical fragmentation and shows its size, so the maintenance window is spent on the largest problems first.
  • Both kinds of fragmentation. Logical fragmentation and page density appear side by side, which separates an out-of-order index from one that is simply half empty.
  • Small indexes stay out. The page-count floor and the leaf-level filter keep tiny indexes from filling the report with noise.
  • Reviewable output. The maintenance_command column is plain text. Read it, edit it, then run it, rather than trusting an automated loop on the first pass.
  • Partition-aware. Each partition is its own row, so a hot partition can be rebuilt alone.

Performance Considerations

  • Lock footprint. The function takes only an Intent-Shared lock on the table in every mode, which is far lighter than the locks an offline rebuild holds.
  • Availability group secondaries. On a readable secondary replica, that Intent-Shared lock can block the redo thread, which needs an exclusive lock on the table. Run the report on the primary, or at a quiet time on the secondary.
  • I/O of the scan. LIMITED is the cheap mode, SAMPLED reads a 1 percent sample for indexes of 10,000 pages or more, and DETAILED reads everything. On a very large database a DETAILED scan reads every page and is a planned job, not an ad hoc one.
  • Reorganize versus rebuild. REORGANIZE is always online, compacts pages to the index's fill factor, and keeps the progress it made if it is stopped, so a large index can be reorganized over several windows. It does not update statistics. REBUILD drops and re-creates the index, and statistics on the index are refreshed with a scan of all rows unless the index is partitioned or the operation is resumable.
  • Offline by default. ALTER INDEX ... REBUILD runs with ONLINE = OFF unless ONLINE = ON is specified, and online operations are not available in every edition of SQL Server. XML and spatial indexes support only ONLINE = OFF.
  • Statistics are the hidden benefit. A performance gain after a rebuild often comes from the fresh statistics rather than from the lower fragmentation, so compare plans before crediting the reduced fragmentation.
  • Small indexes. Up to and including SQL Server 2014, small indexes can sit on mixed extents, so rebuilding or reorganizing them often does not lower the reported fragmentation.

Practical Tips

  • Review the output before running it. Paste selected maintenance_command rows into a query window during the maintenance window, and skip any index whose table is mid-load.
  • Do not lower the fill factor by habit. For most workloads the default of 100 percent performs best; a lower value increases storage, memory and I/O (a fill factor of 50 doubles the disk I/O and memory needed to read and cache the same data). If page splits are hurting, rebuild the index with a fill factor in the 70 to 95 percent range, and check the current value in sys.indexes.fill_factor. On an ever-increasing key such as an IDENTITY column, the free space left by a lower fill factor may never be filled.
  • Rebuild before a shrink, then measure again. If a file must be shrunk, rebuilding or reorganizing first raises page density, so the shrink has fewer pages to move and finishes faster. Run this report again after the shrink, because moving pages can leave indexes fragmented.
  • Treat heaps separately. Heap fragmentation is extent fragmentation, avg_fragmentation_in_percent is NULL for heaps in SAMPLED mode, and forwarded_record_count is NULL in LIMITED mode. DETAILED is the mode that returns both values for a heap, so use it for a heap that takes frequent updates and watch the forwarded-record count.
  • Set MAXDOP on large rebuilds. A higher MAXDOP on ALTER INDEX ... REBUILD can shorten the rebuild at the price of more CPU, so set it deliberately on a busy server.
  • Keep history. Insert the report into a table from a weekly SQL Server Agent job. A table that returns to 30 percent a week after a rebuild is a candidate for a fill factor change or a different key, not for a more frequent rebuild.
  • Use a maintenance procedure for routine runs. For scheduled production maintenance, a parameterized procedure such as IndexOptimize adds per-level actions, online-then-offline fallbacks and logging that this report does not.

Conclusion

This query ranks the large rowstore indexes by logical fragmentation, shows page density next to it, and writes the ALTER INDEX statement to match, using a sampled scan that stays cheap on big indexes and a full scan on small ones. Use it as the measuring step before index maintenance, then reorganize or rebuild the short list it produces.

References

Posts in this series