Index fragmentation Statistics

SELECT Index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_ Pages,page_count,record_count,avg_page_space_used_in_percent
From Sys.dm_db_index_physical_stats (db_id (' Advanced6 '), object_id (' table_2 '), Null,null, ' Sampled ')

When Avg_fragmentation_in_percent is greater than 40%, it is often necessary to collate and rebuild the index, as follows:
ALTER INDEX pk_table_2 on table_2 REBUILD

Index fragmentation Statistics