Checking fragmentation in SQL Server 2005

SQL Server 2005 provides sys.dm_db_index_physical_stats DMV (dynamic management view) which can be used to check index fragmentation.

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('YourDatabaseName'), NULL, NULL, NULL , NULL);

If the avg_fragmentation_in_percent > 30 then Rebuild the index

If the avg_fragmentation_in_percent >5 and <30 then Reorganize the index

In a nutshell, rebuilding an index takes more server resources than reorganizing.