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 -- SELECT object_name(a.object_id) AS table_name, a.index_id, b.name AS index_name, b.type_desc, a.partition_number, a.avg_fragmentation_in_percent, 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.OBJECT_ID, 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 -- --