This is a great blog post by one of the members of the SQL Server team explaining when and where SQL Server 2005 Indexes should be used. The topics covered are:
- How can I find out whether my SQL Indexes are useful?
- Do I have any tables or indexes that are not uses (or rarely)?
- What is the cost to benefit of using indexes in SQL Server 2005?
- Do I have hot spots or index contention?
- Could I benefit from more or less indexes?
The full blog post can be read here. Again a must read for anyone who wants to gain true performance from their SQL Server.