Find Index Fragmentation for All Databases




How to Find Index Fragmentation for All Database

1. Steps to Find Index Fragmentation for All Database

There is possibility that indexes will not give desired performance due to high fragmentation. Below SQL script will provide you Index fragmentation details for all databases where index fragmentation is greater than 30%. It would be helpful while scheduling or determining maintenance task of reindexing. It helps you to determine whether Indexes should be rebuild or reorganized. Normally Indexes should be rebuild if fragmentation is greater than 30-40%.

———————————————————————————————————————————

exec master.sys.sp_MSforeachdb ‘ USE [?]

SELECT db_name() as DatabaseName, OBJECT_NAME (sysst.object_id) as ObjectName,

sysst.index_id, sysind.name as IndexName,

avg_fragmentation_in_percent, index_type_desc

FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS sysst

JOIN sys.indexes AS sysind

ON sysst.object_id = sysind.object_id AND sysst.index_id = sysind.index_id

WHERE b.index_id <> 0 and avg_fragmentation_in_percent >30’
go

——————————————————————————————————————–

2. Steps to Find Index Fragmentation for Specific Database

Use below query if you want to find Index fragmentation for specific DB. Just replace tempDB by your DB name.

————————————————————————————————————————————-

SELECT db_name(ma.database_id), ma.object_id, ma.index_id, su.name, ma.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N’tempDB‘),NULL, NULL, NULL, NULL) AS ma
JOIN sys.indexes AS su ON ma.object_id = su.object_id AND ma.index_id = su.index_id and su.index_id <> 0

and avg_fragmentation_in_percent >30 order by ma.object_id;
GO

————————————————————————————————————————————–

 

3. Steps to Find Index Fragmentation for Specific Table in Database

Use below query if you want to find Index fragmentation for specific Table in DB. Just replace tempDB by your DB name and replace tempDB.ABC by table name.

————————————————————————————————————————————

SELECT db_name(ma.database_id), ma.object_id, ma.index_id, su.name, ma.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N’tempDB‘),OBJECT_ID(N’tempDB.ABC‘), NULL, NULL, NULL) AS ma
JOIN sys.indexes AS su ON ma.object_id = su.object_id AND ma.index_id = su.index_id and su.index_id <> 0

and avg_fragmentation_in_percent >30 order by ma.object_id;
GO

————————————————————————————————————————————