There are two ways to optimize your indexes in SQL Server 2005:
- ALTER INDEX … REORGANIZE
- ALTER INDEX … REBUILD
Which one should you choose? According to BOL, it depends on how fragmented they are. The avg_fragmentation_in_percent column from the sys.dm_db_index_physical_stats() system function lets you know how fragmented your indexes are. If the value is below 5.0, then there is no need to defragment the index. If the value is between 5.0 and 30.0, then “ALTER INDEX … REORGANIZE”, is the best option. If the fragmentation is greater than 30.0, then “ALTER INDEX … REBUILD” is the best option.
REORGANIZE has a couple of benefits. REORGANIZE will log only the pages that changed, while REBUILD will log the whole index. REORGANIZE can be done online, while REBUILD can’t (unless you add the ONLINE=ON option for Enterprise Edition installations).
However REBUILD will update the statistics of the index which lead to better execution plans, while REORGANIZE does not.