SQL Server – List all tables and the fragmentation percentage

This is another check you should perform once databases start to get large, as having fragmented indexes have a huge performance hit.

For more on what fragmented indexes are, read this fantastic article.

After significant trial and error we have come up with an SQL script that lists all indexes for every table and shows the percentage fragmentation for the selected database on your server in one go!

 

SELECT
a.database_id,
db_name(a.database_id) As DatabaseName,
a.page_count,
a.OBJECT_ID,
a.index_id,
b.name,
a.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
INNER JOIN sys.indexes AS b ON a.OBJECT_ID = b.OBJECT_ID AND a.index_id = b.index_id
WHERE
a.database_id = DB_ID()
ORDER BY
a.OBJECT_ID

Leave a Reply