Database Technologies‎ > ‎MSSQL‎ > ‎

Index Rebuild and Re-org

System function sys.dm_db_index_physical_stats provides us with detailed report on the index segment
 
 
avg_fragmentation_in_percent
 The percent of logical fragmentation (out-of-order pages in the index).
 
As per Microsoft recommendation
if avg_fragmentation_in_percent <= 30 %  Then Re-organize the index, using alter index reorganize
if avg_fragmentation_in_percent  >  30 %  Then Re-Build the index using alter index rebuild
 
 
 

SELECT

 a.index_id, name, avg_fragmentation_in_percent

FROM

 sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'<schema>.<table_name>'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

GO

Comments