SQLServer : Finding Index Fragmentation for All Databases

by bitznbitez

Here is a query to find the index fragmentation for all indexes in all databases on a SQLServer.  Note that the dm_db_index_physical_stats() can be resource intensive so run this at an off peak time.

create table #tempFragmentation
  (
     [databasename] varchar(128)
	,[tablename] varchar(128)
	,[indexname] varchar(128)
	,[indexfillfactor] integer
	,[rowscount] bigint
	,[avg_fragmentation_in_percent] float
	,[page_count] integer

  );

Exec sp_msforeachdb '
  use [?]

  insert into #tempFragmentation(

     [databasename] 
	,[tablename] 
	,[indexname] 
	,[indexfillfactor] 
	,[rowscount] 
	,[avg_fragmentation_in_percent] 
	,[page_count] 

  )

  SELECT
      db_name() AS DatabaseName
    , B.name AS TableName
    , C.name AS IndexName
    , C.fill_factor AS IndexFillFactor
    , D.rows AS RowsCount
    , A.avg_fragmentation_in_percent
    , A.page_count
    FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
    INNER JOIN sys.objects B
      ON A.object_id = B.object_id
    INNER JOIN sys.indexes C
      ON B.object_id = C.object_id AND A.index_id = C.index_id
    INNER JOIN sys.partitions D
      ON B.object_id = D.object_id AND A.index_id = D.index_id
    WHERE C.index_id > 0
      and A.avg_fragmentation_in_percent >50

  ;

'

select * from #tempFragmentation order by avg_fragmentation_in_percent desc;

--drop table #tempFragmentation;

Note that the drop table is commented out because you may want to keep this temp table around for a while given how long it takes to build it and run a few more queries on it.

Advertisements