Hurray.. to John and Michael.. Great Explanation.

I am Just curious to know.. how long does it take you to REBUILD your indexes on these BIG Tables.. and How often do you rebuild your indexes.

with such big tables , you Database Size should be HUGE, so if you RUN SHRINKFILE or SHRINKDATABASE, it can introduce Fragmentation too...

So you should consider running REINDEX after you shrink operations.

SQL 2005 has some super DMV's and can give you lots of info as already discussed.

sys.dm_db_index_physical_stats

sys.dm_db_index_operational_stats

sys.dm_db_index_usage_stats