The following tools can be used to help optimize a query:
Index Tuning Wizard
SQL Server Profiler
Stats Time tool
The Index Tuning Wizard analyzes a query and makes index recommendations. (See Chapter 18, “Understanding Indexes,” for more information.) It's like having a DBA on your staff who never sleeps and never asks for a raise!
New and experienced DBAs should look at the Index Tuning Wizard. Overall, the Index Tuning Wizard does a good job of recommending indexes. However, the wizard should be used as a starting point for query optimization and not as the ending point. The wizard is not always 100% correct. For example, it will occasionally make a mistake by not recommending ...