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_percentFROM 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 |
Database Technologies > MSSQL >