How to Check Index Fragmentation in a SQL Server Database

Check Index Fragmentation

Check Index Fragmentation

If we run INSERT, UPDATE or DELETE queries frequently than we can see indexes on the particular table are fragmented. And the SQL query may run slower as they may choose the non-optimal execution plan. Below script can be used for checking how much percentages of an indexes gets fragmented on a database. so that we can rebuild or reorganise the indexes.

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, AS IndexName, indexstats.index_type_desc AS IndexType,
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 10--You can specify the percent as you want
AND is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Rebuild fragmented Indexes

Leave a Reply

Your email address will not be published. Required fields are marked *