Microsoft's Database Engine Tuning Advisor is an immensely useful tool. Features include:
- recommend indexes based on query analysis
- analyze proposed schema changes
- query tuning recommendations
- customization of recommendations
- performance reports
A tutorial is available on MSDN.