SQL Server Index Fragmentation: dm_db_index_physical_stats

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

Report Index Fragmentation Across All Tables in SQL Server

This script queries sys.dm_db_index_physical_stats to report external and internal fragmentation for every index in the current database, filtering to indexes with significant fragmentation and enough pages to make maintenance worthwhile.

Purpose and Overview

Index fragmentation is one of the most common causes of gradually degrading query performance in SQL Server. As rows are inserted, updated, and deleted, index pages become out of order (external fragmentation) and partially empty (internal fragmentation). Both conditions force SQL Server to read more pages than necessary to satisfy a query. The sys.dm_db_index_physical_stats dynamic management function scans index structures and reports fragmentation levels, giving DBAs the data needed to decide which indexes should be rebuilt and which should be reorganized. This script, adapted from the DBA-Scripts collection by Bulent Gucuk, runs against all tables in the current database and filters the output to actionable results.

Code Breakdown

The complete script is shown below, followed by an explanation of each section.

 1USE [YourDatabase];
 2GO
 3SELECT
 4    DB_NAME(ips.database_id)                    AS database_name,
 5    OBJECT_NAME(ips.object_id)                  AS table_name,
 6    i.name                                      AS index_name,
 7    ips.index_id,
 8    ips.index_type_desc,
 9    ips.avg_fragmentation_in_percent            AS external_fragmentation_pct,
10    ips.avg_page_space_used_in_percent          AS internal_fragmentation_pct,
11    ips.page_count
12FROM sys.dm_db_index_physical_stats(
13    DB_ID(),    -- current database
14    NULL,       -- all objects
15    NULL,       -- all indexes
16    NULL,       -- all partitions
17    'SAMPLED'   -- sampling mode: LIMITED, SAMPLED, or DETAILED
18) AS ips
19INNER JOIN sys.indexes AS i
20    ON ips.object_id = i.object_id
21   AND ips.index_id = i.index_id
22WHERE ips.avg_fragmentation_in_percent > 10
23    AND ips.page_count > 100
24ORDER BY ips.avg_fragmentation_in_percent DESC;

sys.dm_db_index_physical_stats Parameters

sys.dm_db_index_physical_stats is a table-valued function that takes five parameters:

ParameterValue usedMeaning
database_idDB_ID()Current database; pass NULL for all databases
object_idNULLAll tables and views
index_idNULLAll indexes on each object
partition_numberNULLAll partitions
mode'SAMPLED'Random page sampling — faster than DETAILED, more accurate than LIMITED

Mode choices:

  • LIMITED — reads only allocation metadata; very fast, but does not return avg_fragmentation_in_percent for all index types
  • SAMPLED — samples a percentage of leaf-level pages; balances speed and accuracy for most environments
  • DETAILED — scans every leaf-level page; most accurate but can cause significant I/O on large indexes

Use SAMPLED for routine maintenance scripts and DETAILED only when you need precise numbers for a specific index.

Key Columns

  • avg_fragmentation_in_percent — external fragmentation: the percentage of out-of-order pages in the index leaf level. High values mean SQL Server must perform extra reads to follow the logical index order. This is the primary metric for deciding whether maintenance is needed.
  • avg_page_space_used_in_percent — internal fragmentation: how full each page is on average. A value of 60% means pages are 40% empty, wasting storage and increasing the number of pages that must be read for a full scan.
  • page_count — number of leaf-level pages in the index. Filtering on page_count > 100 excludes very small indexes where fragmentation has negligible performance impact and maintenance overhead is not justified.
  • index_type_desc — describes the index structure: CLUSTERED INDEX, NONCLUSTERED INDEX, HEAP, XML INDEX, etc.

Joining sys.indexes

sys.dm_db_index_physical_stats does not return the index name — only the index_id. The INNER JOIN to sys.indexes on object_id and index_id retrieves the human-readable index name. This join also implicitly excludes heaps (index_id = 0) from the result when i.name is referenced, since heaps have no entry in sys.indexes with a name.

Filters

WHERE ips.avg_fragmentation_in_percent > 10 focuses the output on indexes with meaningful fragmentation. The standard threshold guidelines are:

  • 10–30%: consider ALTER INDEX ... REORGANIZE
  • Above 30%: consider ALTER INDEX ... REBUILD

AND ips.page_count > 100 eliminates tiny indexes. An index with five pages at 80% fragmentation is not worth maintaining — the performance impact is negligible and the maintenance overhead is not zero.

Key Benefits and Use Cases

  • Identifies fragmented indexes across an entire database in a single query
  • External and internal fragmentation columns support both REORGANIZE and REBUILD decisions
  • page_count filter prevents wasting maintenance time on insignificant indexes
  • SAMPLED mode is fast enough to run during low-activity windows without causing I/O storms
  • Results can be fed directly into a maintenance script that generates ALTER INDEX statements
  • Works on all SQL Server editions; no Enterprise-only features required

Performance Considerations

  • I/O cost of DETAILED mode: Running sys.dm_db_index_physical_stats in DETAILED mode on a large database can generate substantial I/O and take several minutes. Use SAMPLED for routine checks and DETAILED only when investigating a specific index.
  • Online vs. offline rebuild: ALTER INDEX ... REBUILD is an offline operation by default, blocking all access to the table during the rebuild. On Enterprise and Developer editions, ALTER INDEX ... REBUILD WITH (ONLINE = ON) allows concurrent access. REORGANIZE is always online.
  • Fill factor: After a REBUILD, the index is compacted to the fill factor setting. A fill factor of 80 leaves 20% free space on each page to reduce future fragmentation from inserts. Choose a fill factor based on the insert/update pattern of the table.
  • Ola Hallengren's solution: For production environments, consider Ola Hallengren's IndexOptimize, which wraps sys.dm_db_index_physical_stats in a stored procedure with configurable thresholds, fragment limits, and online rebuild support.

Practical Tips

To generate ALTER INDEX statements directly from the output, extend the query with a CASE expression:

 1SELECT
 2    OBJECT_NAME(ips.object_id)           AS table_name,
 3    i.name                               AS index_name,
 4    ips.avg_fragmentation_in_percent     AS fragmentation_pct,
 5    ips.page_count,
 6    CASE
 7        WHEN ips.avg_fragmentation_in_percent > 30
 8            THEN 'ALTER INDEX ' + QUOTENAME(i.name)
 9                 + ' ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id))
10                 + '.' + QUOTENAME(OBJECT_NAME(ips.object_id))
11                 + ' REBUILD;'
12        WHEN ips.avg_fragmentation_in_percent > 10
13            THEN 'ALTER INDEX ' + QUOTENAME(i.name)
14                 + ' ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id))
15                 + '.' + QUOTENAME(OBJECT_NAME(ips.object_id))
16                 + ' REORGANIZE;'
17    END AS maintenance_command
18FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
19INNER JOIN sys.indexes AS i
20    ON ips.object_id = i.object_id AND ips.index_id = i.index_id
21INNER JOIN sys.objects AS o
22    ON ips.object_id = o.object_id
23WHERE ips.avg_fragmentation_in_percent > 10
24    AND ips.page_count > 100
25ORDER BY ips.avg_fragmentation_in_percent DESC;

Copy the maintenance_command column output and execute it during a maintenance window, or pipe the results into a cursor that executes each statement automatically.

Schedule this script as a SQL Server Agent job that runs weekly and writes results to a monitoring table. Track fragmentation trends over time to identify tables that fragment faster than expected — a sign of a high-churn workload that may need a lower fill factor or more frequent maintenance.

Conclusion

sys.dm_db_index_physical_stats is the standard tool for assessing index health in SQL Server. By running it in SAMPLED mode against all objects in a database and filtering on page count and fragmentation threshold, this script produces a focused list of indexes that need attention — without the noise of negligible fragmentation on tiny indexes. Use the generated ALTER INDEX statements to build a targeted maintenance script, and schedule regular runs to catch fragmentation before it affects query performance.

References

Posts in this series