SQL Server Index Fragmentation

sql-server-index-fragmentation.sql

select 
	ps.object_id,
	idx.name,
	idx.type_desc,
	ps.alloc_unit_type_desc,
	ps.avg_fragmentation_in_percent,
	ps.fragment_count,
	ps.avg_fragment_size_in_pages,
	ps.page_count 
from 
	sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) ps
join 
	sys.indexes idx 
		on idx.object_id = ps.object_id
		and	idx.index_id = ps.index_id
order by 
	avg_fragmentation_in_percent desc, 
	page_count desc;