Quick Check on Fragmentation

Quick Check on Fragmentation

Just a quick check on sys.dm_db_index_physical_stats, returning the percentage of index fragmentation that is detected, for the given TABLENAME.


--Check index fragmentation
SELECT 
    OBJECT_NAME(i.[object_id]) ObjectName,
    s.index_type_desc IndexType,
    i.name IndexName,
    s.partition_number [Partition#],
    ROUND(s.avg_fragmentation_in_percent, 2) [Fragmentation%]
FROM 
    sys.dm_db_index_physical_stats 
        (
        DB_ID(),
        OBJECT_ID('dbo.TABLENAME', 'U'), << CHANGE TO YOUR TABLE
        NULL,
        NULL,
        NULL
        ) AS s
    INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE
    s.index_id = i.index_id;