Find index fragmentation details-slow index performance

SQL Server-2005-find index fragmentation details-slow index performance

Just a day ago, while using one index I was not able to get the desired performance from the table where it was applied. I just looked for its fragmentation and found it was heavily fragmented. after I reorganized index it worked perfectly fine. here is the quick script I wrote to find fragmentation of the database for all the indexes.

SELECT ps.database_id, ps.OBJECT_ID,ps.index_id, b.name,ps.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS psINNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_IDAND ps.index_id = b.index_idWHERE ps.database_id = DB_ID()ORDER BY ps.OBJECT_IDGO

 

You can rebuild or reorganize index and improve performance. Here is articleSQL Server-difference between index rebuild and index reorganize explained with T-SQL scriptFor how to do it.

Find index fragmentation details-slow index performance