How to check fragmentation on SQL Server 2005
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 |
