How to detect and resolve Database Index fragmentation
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
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.
See Also