Database external fragmentation overview

use MyDatabase




go


select top 50

avg_fragmentation_in_percent*s.page_count/100.00 fragmented_page_count

,o.name as TableName

,i.name as IndexName

,s.partition_number

,s.avg_fragmentation_in_percent

,s.page_count

,s.index_depth

,s.index_level

,s.index_type_desc

,d.name as DataBaseName

,s.*

from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'limited') s

--sys.dm_db_index_physical_stats(db_id(), object_id('MyTableInCurrentDB'), null, null, 'limited') s

inner join sys.databases d on d.database_id=s.database_id

inner join sys.objects o on o.object_id=s.object_id

left join sys.indexes i on i.object_id=s.object_id and i.index_id=s.index_id

where -- have large external fragmentation

s.avg_fragmentation_in_percent>10

-- ignore small table/index

and s.page_count>1

-- ignore heap table

and s.index_id>0

order by avg_fragmentation_in_percent*s.page_count desc