Take the following steps to tune a database: 1. Run SQL Server Profiler from Start -programs – Microsoft SQL Server 2005 – Performance Tools – SQL Server Profiler.
2. In the SQL Server Profiler window, go to File – New Trace option.
3. In the Connect to Server window, select server name from the Server name drop-down list and click the Connect button.
4. In the TraceProperties dialog box, mention the Trace name. Click the Run button.
5. The trace will be created. In order to save the trace, go to File – Save As -Trace File option.
6. In the Save As dialog box, specify the path and name of the trace file. Click the Save button.
7. In the SQL Server Profiler window, go to Tools – Database Engine Tuning Advisor option. Note: You can also open Database Engine Tuning Advisor from Start – Programs – Microsoft SQL Server 2005 – Performance Tools – Database Engine Tuning Advisor.
8. In the Connect to Server window, select server name from the Server name drop-down list and click the Connect button.
9. In the Database Engine Tuning Advisor window, select the database for which you want to create a workload.
10. In order to select the trace file for creating a workload, select the Browse button. In the Select Workload File dialog box, select the trace file.
11. The location and name of the file is displayed in the text box. Click the Start Analysis button in the toolbar.
12. Click the Progress tab to view the tuning progress.
13. Click the Recommendations tab to view recommendations for index and partitions.
14. Click the Reports tab to view the report of the tuning.See the following video for more details: