Checking Index Fragmentation in SQL Server

By adminOn January 7, 2024

You can use the sys.dm_db_index_physical_stats function to get index fragmentation information in SQL Server. Here’s a query that lists index fragmentation for all tables in the current database:

SELECT 
    dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN 
    sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN 
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN 
    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
WHERE 
    indexstats.database_id = DB_ID()
ORDER BY 
    indexstats.avg_fragmentation_in_percent desc

This query returns the schema name, table name, index name, average fragmentation in percent, and page count for each index in the current database. The results are ordered by the average fragmentation in percent in descending order.

Related