What I wanted to find is the most fragmented indexes, including the partition number (for the partitioned tables) and size for each index.\u00a0 I want to bring back only the information that lets me make a quick decision on how to handle it.\u00a0 The query is set to bring back only those indexes with fragmentation greater then 5 percent set in the WHERE clause.<\/p>\n
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).<\/p>\n
--\r\n -- Highest Index Fragmentation % with Partition Number and Index Size\r\n --\r\n SELECT\r\n object_name(a.object_id) AS table_name,\r\n a.index_id,\r\n b.name AS index_name,\r\n b.type_desc,\r\n a.partition_number,\r\n a.avg_fragmentation_in_percent,\r\n FS.Indexsize as 'IndexSize(MB)'\r\n FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') a\r\n JOIN sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id\r\n join (SELECT\r\n i.OBJECT_ID,\r\n i.name AS IndexName,\r\n i.index_id AS IndexID,\r\n p.partition_number as partition_number,\r\n (8 * SUM(a.used_pages))\/1024 AS 'Indexsize'\r\n FROM sys.indexes AS i\r\n JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id\r\n JOIN sys.allocation_units AS a ON a.container_id = p.partition_id\r\n GROUP BY i.OBJECT_ID,i.index_id,i.name,p.partition_number) AS FS\r\n on a.object_id = FS.OBJECT_ID and b.name = FS.IndexName and a.partition_number = FS.partition_number\r\n where a.avg_fragmentation_in_percent > 5\r\n order by a.avg_fragmentation_in_percent desc,object_name(a.object_id), a.index_id, b.name, b.type_desc, a.partition_number\r\n --\r\n --<\/pre>\n
![\"Query_For_Index_Fragmentation\"](\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2014\/07\/Query_For_Index_Fragmentation.jpg\")
<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"