	Product(s):	APM Implementation and Performance Management
	Version(s):	All Versions
	Environment:	SQL Server
	Area:	Database Performance
	Subarea:	N\A

Problem

How to detect and resolve Database Index fragmentation

Solution

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'YourDBName'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

Weekly Maintenance:

Rebuild indexes where avg fragmentation is >30%. This is recommended to be done on a weekly basis. Exclusive access is required for defragging.

Performance - detect Database Index fragmentation

Problem

Solution