When Insert, update or Delete operations are performed on tables then table fragmentation is occurred. Due to fragmentation, response time increase that decrease the performance of SQL Server. Before performing any type of defragmentation task, first we should have the basic knowledge fragmentation level of tables. We can use below query to find out the fragmentation information of tables:
Syntax
SELECT OBJECT_NAME(OBJECT_ID) Table_Name, index_type_desc as Index_Type, record_count as Numbers_Of_Records, avg_record_size_in_bytes,
avg_fragmentation_in_percent, avg_page_space_used_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('Table_Name'), NULL, NULL, NULL , 'SAMPLED')
Order By Table_Name;
Example