TSQL Query to Find Fragmented Indexes Including Partitioned Indexes

What I wanted to find is the most fragmented indexes, including the partition number (for the partitioned tables) and size for each index.  I want to bring back only the information that lets me make a quick decision on how to handle it.  The query is set to bring back only those indexes with fragmentation greater then 5 percent set in the WHERE clause.

This is a query that I have forged together from a couple of queries found on the internet (don’t remember where I found the queries, so that I can give credit).

 -- Highest Index Fragmentation % with Partition Number and Index Size
 object_name(a.object_id) AS table_name,
 b.name AS index_name,
 FS.Indexsize as 'IndexSize(MB)'
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') a
 JOIN sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id
 join (SELECT
 i.name AS IndexName,
 i.index_id AS IndexID,
 p.partition_number as partition_number,
 (8 * SUM(a.used_pages))/1024 AS 'Indexsize'
 FROM sys.indexes AS i
 JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
 JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
 GROUP BY i.OBJECT_ID,i.index_id,i.name,p.partition_number) AS FS
 on a.object_id = FS.OBJECT_ID and b.name = FS.IndexName and a.partition_number = FS.partition_number
 where a.avg_fragmentation_in_percent > 5
 order by a.avg_fragmentation_in_percent desc,object_name(a.object_id), a.index_id, b.name, b.type_desc, a.partition_number


Leave a Comment