Indexes on SQL Server

[Svek Web]

The key tables for any sort of database index tuning are :-
sys.dm_db_index_usage_stats
sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats

Key point though - these statistics are cumulative since the last serve restart so don't try tuning on a cold server!

Resources
dm_db_index_usage_stats - http://msdn.microsoft.com/en-us/library/ms188755.aspx
dm_db_missing_index_details - http://msdn.microsoft.com/en-us/library/ms345434.aspx
dm_db_missing_index_groups http://msdn.microsoft.com/en-us/library/ms345407.aspx
dm_db_missing_index_group_stats  http://msdn.microsoft.com/en-us/library/ms345421.aspx

Note that missing index details will be captured automatically unless SQL is started with the -x flag and that there are quite a few limitations to the missing indexes so if you've got over 500 missing index groups it's not really going to help you