Friday, 17 October 2014

Defragmentation


Query to find the fragmentation percent

SELECT OBJECT_NAME(A.OBJECT_ID),avg_fragmentation_in_percent,* 
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN SYS.INDEXES B on a.index_id = b.index_id
AND A.OBJECT_ID= B.OBJECT_ID 
AND database_id = DB_ID()
AND (index_type_desc ='CLUSTERED INDEX' or index_type_desc ='NONCLUSTERED INDEX')
ORDER BY 2
To Reorganize the Index can use the below code
ALTER INDEX INDEXNAME
ON TABLENAME REORGANIZE

Useful Links

  1. Fragmentation and Defragmentation steps video