SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('
your_db_name'), OBJECT_ID('
your_one_table'), NULL, NULL, NULL);
Reference Values (in %) | Action | SQL statement |
avg_fragmentation_in_percent > 5 AND < 30 | Reorganize Index | ALTER INDEX REORGANIZE |
avg_fragmentation_in_percent > 30 | Rebuild Index | ALTER INDEX REBUILD |