Another great tool provided with SQL Server is the Database Engine Tuning Advisor (DTA). The DTA will evaluate a workload file, which can be either a T-SQL script or a Profiler trace file, in order to generate recommendations for indexes and database structures that will improve the performance of the database. You must give the DTA what it needs to produce the best results. For instance, if you give it a workload file that does not represent real-world activity, you will not get recommendations that give improvements in your real-world database.
The DTA tool is used by creating a workload file, then running an analysis, and then either applying, saving, or saving and applying the recommendations it provides.