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