sys.dm_db_index_physical_stats - Usage & Examples

sys.dm_db_index_physical_stats interrogates the Index Allocation Map (IAM) and Page Free Space (PFS) structures to calculate fragmentation metrics. The DMV operates in three scan modes: LIMITED (fastest, metadata only), SAMPLED (statistical sampling), and DETAILED (full page scan). When executed, it accesses the buffer pool first for cached pages, then performs physical I/O to storage for uncached data.

The fragmentation calculations occur at the storage engine level by examining page linkages in doubly-linked lists. Logical fragmentation measures out-of-order pages in the logical sequence, while extent fragmentation calculates physically non-contiguous 64KB extents. The avg_page_space_used_in_percent metric requires reading actual page headers to determine free space utilization.

SQL Server 2019 added better memory grant estimation for the DMV operations. Note that this DMV is single-threaded regardless of scan mode, so execution time scales linearly with the amount of data examined — plan accordingly for large indexes.

The ghost_record_count column reflects records marked for deletion but not yet cleaned by the ghost cleanup process. This directly impacts page density calculations and can artificially inflate fragmentation metrics if ghost cleanup is lagging due to long-running transactions or disabled ghost cleanup tasks.

-- Check overall database fragmentation summary
SELECT 
    DB_NAME(database_id) as database_name,
    COUNT(*) as total_indexes,
    AVG(avg_fragmentation_in_percent) as avg_fragmentation,
    COUNT(CASE WHEN avg_fragmentation_in_percent > 30 THEN 1 END) as highly_fragmented_indexes
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED')
WHERE index_id > 0
GROUP BY database_id;

-- Identify specific indexes requiring maintenance
SELECT 
    OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) as table_name,
    i.name as index_name,
    avg_fragmentation_in_percent,
    page_count,
    avg_page_space_used_in_percent,
    ghost_record_count,
    CASE 
        WHEN avg_fragmentation_in_percent > 30 THEN 'REBUILD'
        WHEN avg_fragmentation_in_percent > 10 THEN 'REORGANIZE'
        ELSE 'NO ACTION'
    END as recommended_action
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.index_id > 0 
    AND ps.page_count > 1000  -- Skip small indexes
    AND ps.avg_fragmentation_in_percent > 10
ORDER BY ps.avg_fragmentation_in_percent DESC;

-- Detailed analysis of specific problematic table
DECLARE @ObjectID INT = OBJECT_ID('YourTable');
SELECT 
    index_type_desc,
    alloc_unit_type_desc,
    index_depth,
    avg_fragmentation_in_percent,
    fragment_count,
    avg_fragment_size_in_pages,
    page_count,
    avg_page_space_used_in_percent,
    record_count,
    ghost_record_count,
    min_record_size_in_bytes,
    max_record_size_in_bytes,
    avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), @ObjectID, NULL, NULL, 'DETAILED');

-- Monitor fragmentation trends over time (store results in temp table)
SELECT 
    GETDATE() as sample_time,
    OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) as table_name,
    i.name as index_name,
    avg_fragmentation_in_percent,
    page_count,
    record_count
INTO #FragmentationHistory
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.index_id > 0 AND ps.page_count > 100;

-- Identify extent fragmentation for storage optimization
SELECT 
    OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) as table_name,
    i.name as index_name,
    avg_fragmentation_in_percent as logical_fragmentation,
    (forwarded_record_count * 100.0 / NULLIF(record_count, 0)) as forwarded_record_percent,
    page_count,
    compressed_page_count,
    avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.index_id = 0  -- Heap tables only
    AND ps.page_count > 1000
ORDER BY forwarded_record_percent DESC;

Automated Index Maintenance Script Generates maintenance commands based on fragmentation thresholds. Always test maintenance windows and resource impact before production deployment.

-- Generate dynamic index maintenance commands
DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL = @SQL + 
    CASE 
        WHEN avg_fragmentation_in_percent > 30 THEN 
            'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + '] REBUILD WITH (ONLINE = ON, MAXDOP = 4);' + CHAR(13)
        WHEN avg_fragmentation_in_percent > 10 THEN 
            'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + '] REORGANIZE;' + CHAR(13)
    END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
INNER JOIN sys.objects o ON ps.object_id = o.object_id
WHERE ps.index_id > 0 
    AND ps.page_count > 1000
    AND ps.avg_fragmentation_in_percent > 10;

PRINT @SQL;
-- EXEC sp_executesql @SQL;  -- Uncomment to execute

Selective Rebuild for Critical Indexes Targets only high-impact indexes during maintenance windows. Monitor transaction log growth during execution.

-- Rebuild only critical indexes with high fragmentation
DECLARE @DatabaseName NVARCHAR(128) = DB_NAME();
DECLARE @MaxDOP INT = 4;
DECLARE @OnlineRebuild BIT = 1;

WITH CriticalIndexes AS (
    SELECT 
        ps.object_id, 
        ps.index_id,
        OBJECT_SCHEMA_NAME(ps.object_id) as schema_name,
        OBJECT_NAME(ps.object_id) as table_name,
        i.name as index_name,
        ps.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ps
    INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
    WHERE ps.avg_fragmentation_in_percent > 30
        AND ps.page_count > 5000  -- Large indexes only
        AND i.is_disabled = 0
)
SELECT 
    'ALTER INDEX [' + index_name + '] ON [' + schema_name + '].[' + table_name + '] REBUILD WITH (' +
    CASE WHEN @OnlineRebuild = 1 THEN 'ONLINE = ON, ' ELSE '' END +
    'MAXDOP = ' + CAST(@MaxDOP AS VARCHAR(2)) + ', FILLFACTOR = 90);' as rebuild_command
FROM CriticalIndexes
ORDER BY avg_fragmentation_in_percent DESC;

Update Statistics After Maintenance Updates statistics on maintained indexes to ensure optimal query plans. Execute immediately after index operations complete.

-- Update statistics for recently maintained indexes
DECLARE @StatsSQL NVARCHAR(MAX) = '';
SELECT @StatsSQL = @StatsSQL + 
    'UPDATE STATISTICS [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + '] [' + i.name + '] WITH FULLSCAN;' + CHAR(13)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
INNER JOIN sys.objects o ON ps.object_id = o.object_id
INNER JOIN sys.dm_db_stats_properties(ps.object_id, ps.index_id) sp ON sp.object_id = ps.object_id AND sp.stats_id = ps.index_id
WHERE ps.index_id > 0 
    AND sp.last_updated < DATEADD(hour, -24, GETDATE())  -- Statistics older than 24 hours
    AND ps.page_count > 1000;

EXEC sp_executesql @StatsSQL;

Configure SQL Server Agent jobs to monitor fragmentation weekly using SAMPLED mode on production systems. Avoid DETAILED mode on large databases during business hours as it generates significant I/O overhead. Implement threshold-based maintenance: reorganize indexes between 10-30% fragmentation, rebuild above 30%.

Set appropriate FILLFACTOR values during index creation to accommodate growth patterns. OLTP systems typically use 85-95% fill factor, while data warehouses can use 100%. Monitor page splits through sys.dm_db_index_operational_stats to identify inadequate fill factors.

Enable page compression on large, stable tables to reduce storage footprint and improve I/O efficiency. Page compression inherently reduces fragmentation by maximizing data density per page. Test compression ratios using sp_estimate_data_compression_savings before implementation.

Implement partitioning for very large tables (>100GB) to isolate maintenance operations to specific partitions. This reduces maintenance windows and allows sliding window maintenance strategies. Partition elimination also improves query performance by reducing scan overhead.

Schedule maintenance operations during low-activity periods and monitor resource utilization. Use Resource Governor to limit maintenance job impact on production workloads. Consider using resumable index operations in SQL Server 2017+ for large indexes that cannot complete within maintenance windows.