SQL Server DMV Query to Output Index Fragmentation by Database / Table

How to Check SQL Server Index Fragmentation Using DMV Queries

Index fragmentation is one of the most common causes of query performance degradation in SQL Server environments. You can measure it using sys.dm_db_index_physical_stats, a dynamic management view (DMV) that returns detailed fragmentation data for indexes across any database. As a general rule, fragmentation above 10% warrants attention, and anything above 30% typically requires a rebuild rather than a reorganise.

Left unchecked, fragmentation accumulates quietly. Pages fill unevenly, the storage engine reads more data than necessary, and queries that once ran in milliseconds start creeping into seconds. The problem compounds over time, particularly in databases with heavy insert, update, and delete activity. Knowing how to query index fragmentation accurately is a fundamental skill for any DBA or system administrator managing SQL Server.

What Is Index Fragmentation in SQL Server?

When SQL Server stores data in B-tree index structures, it organises data across 8KB pages. As rows are inserted, updated, and deleted, these pages can become disordered or partially empty. There are two types of fragmentation to understand:

• Logical fragmentation - index pages are out of order on disk, forcing the storage engine to jump around rather than read sequentially. This is what avg_fragmentation_in_percent measures.
• Internal fragmentation - pages are not filled to capacity, wasting space and causing the engine to read more pages than necessary. This is reflected in avg_page_space_used_in_percent.

Both types affect performance, but in different ways. Logical fragmentation hits sequential read operations hardest. Internal fragmentation increases the total number of pages the engine must read, which affects both sequential and random access patterns.

How to Query Index Fragmentation Across an Entire Database

The query below uses sys.dm_db_index_physical_stats in DETAILED mode to return fragmentation data for every user table in a database. Run it against the database you want to analyse.

USE AdventureWorks
GO

SELECT
    object_name(IPS.object_id)          AS [TableName],
    SI.name                             AS [IndexName],
    IPS.index_type_desc,
    IPS.avg_fragmentation_in_percent,
    IPS.avg_fragment_size_in_pages,
    IPS.avg_page_space_used_in_percent,
    IPS.record_count,
    IPS.ghost_record_count,
    IPS.fragment_count,
    IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(
    db_id(N'AdventureWorks'), NULL, NULL, NULL, 'DETAILED'
) IPS
JOIN sys.tables  ST WITH (NOLOCK) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (NOLOCK) ON IPS.object_id = SI.object_id
                                  AND IPS.index_id  = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1, 5
GO

A few things worth noting about this query. The DETAILED scan mode is the most thorough option available, but it comes at a cost. On large databases, this query can run for several minutes and will generate significant I/O. If you're running this on a production server during business hours, consider using LIMITED or SAMPLED mode instead. LIMITED is the fastest but doesn't return page-level statistics. SAMPLED gives you a reasonable approximation without the full overhead.

The WHERE ST.is_ms_shipped = 0 filter excludes system tables, so you're only looking at user-defined objects. The NOLOCK hints on the joins are acceptable here since you're reading metadata, not transactional data.

Understanding the Key Columns

How to Check Index Fragmentation for a Single Table

When you want to drill into a specific table rather than scan the entire database, the following query is more targeted and faster to execute. It's particularly useful when a developer reports a specific query is slow and you want to quickly assess the indexes on that table.

DECLARE @db_id     SMALLINT;
DECLARE @object_id INT;

SET @db_id     = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'Production.BillOfMaterials');

IF @object_id IS NULL
BEGIN
    PRINT N'Invalid object';
END
ELSE
BEGIN
    SELECT
        IPS.index_type_desc,
        IPS.avg_fragmentation_in_percent,
        IPS.avg_fragment_size_in_pages,
        IPS.avg_page_space_used_in_percent,
        IPS.record_count,
        IPS.ghost_record_count,
        IPS.fragment_count,
        IPS.avg_fragment_size_in_pages
    FROM sys.dm_db_index_physical_stats(
        @db_id, @object_id, NULL, NULL, 'DETAILED'
    ) AS IPS;
END
GO

The OBJECT_ID() validation at the start is a useful safeguard. If you pass in a table name that doesn't exist or is misspelled, the query exits cleanly with a message rather than returning an empty result set that might be mistaken for "no fragmentation found."

What to Do With the Results

Querying index fragmentation is only half the job. Once you have the data, you need to act on it. Microsoft's general guidance, which aligns with what we see in production environments, is:

• Under 10% fragmentation - no action required.
• 10% to 30% fragmentation - run ALTER INDEX ... REORGANISE. This is an online operation and can be run during business hours on most systems.
• Above 30% fragmentation - run ALTER INDEX ... REBUILD. This is more resource-intensive and, unless you're on Enterprise Edition with the ONLINE = ON option, will take the index offline briefly.

These thresholds aren't absolute. A heavily read table with 15% fragmentation on a busy OLTP system might justify more frequent maintenance than a reporting table sitting at 25%. Context matters. Small indexes, particularly those under 1,000 pages, often don't benefit much from defragmentation at all because the query optimiser is likely to use a table scan anyway.

Running This as Part of Regular Maintenance

Ad-hoc fragmentation checks are useful for troubleshooting, but the real value comes from running these queries on a schedule and using the results to drive automated maintenance. SQL Server Agent jobs combined with Ola Hallengren's free IndexOptimize solution are the most common approach in production environments. That solution dynamically assesses fragmentation levels and chooses between reorganise and rebuild accordingly, which is far more efficient than blanket nightly rebuilds.

If you're running SQL Server 2019 or later, Resumable Index Rebuild is worth investigating. It allows rebuild operations to be paused and resumed, which is particularly useful for large indexes in environments with limited maintenance windows.

Key Takeaways

• Use sys.dm_db_index_physical_stats with DETAILED mode for accurate index fragmentation data. Use SAMPLED or LIMITED on large production databases to reduce overhead.
• The standard thresholds are: under 10% requires no action, 10-30% warrants a reorganise, and above 30% warrants a rebuild.
• avg_fragmentation_in_percent measures logical fragmentation. avg_page_space_used_in_percent measures internal fragmentation. Both matter for performance.
• Small indexes under 1,000 pages rarely benefit from defragmentation. Factor index size into your maintenance decisions.
• Automated, threshold-based maintenance is more effective than scheduled blanket rebuilds. Tools like Ola Hallengren's IndexOptimize implement this approach well.

If index fragmentation is a recurring issue in your environment, it's often a sign that your maintenance strategy needs a review. DBA Services provides SQL Server health checks and ongoing managed support for organisations across Australia, helping teams identify and resolve performance bottlenecks before they become business problems. Get in touch to find out how we can help.