SQL SERVER - 2005 - Find Index Fragmentation Details - Slow Index Performance - script

The following document was from the great Pinal Dave on from the following website: http://www.sqlauthority.com/. This article was extremely useful when I was troubleshooting performance issues in Dynamics GP:


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_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO

You can REBUILD or REORGANIZE Index and improve performance. Here is article SQL SERVER - Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script for how to do it.

Reference : Pinal Dave (http://www.sqlauthority.com/)