How to: View Tuning Output

  • Article

When the Database Engine Tuning Advisor tunes databases, it creates summaries, recommendations, reports, and tuning logs. You can use the tuning log output to troubleshoot Database Engine Tuning Advisor tuning sessions. You can use the summaries, recommendations, and reports to determine whether you want to implement tuning recommendations or continue tuning until you achieve the query performance improvements that you need for your Microsoft SQL Server 2005 installation. The following procedures describe how to view tuning recommendations, summaries, reports, and tuning logs with the Database Engine Tuning Advisor graphical user interface (GUI). You can also use the GUI to view tuning output that is generated by the dta command-line utility. For a step-by-step guided tour through these two interfaces of Database Engine Tuning Advisor, see Database Engine Tuning Advisor Tutorial.

Note

If you use the dta command-line utility and specify that output be written to an XML file by using the -ox argument, you can open and view the XML output file by clicking Open File on the File menu of SQL Server Management Studio. For more information, see Introducing SQL Server Management Studio. For information about the dta command-line utility, see dta Utility.

To view tuning recommendations with the Database Engine Tuning Advisor GUI

  1. Tune a database using the Database Engine Tuning Advisor GUI or the dta command-line utility. See How to: Tune a Database. If you want to use an existing tuning session, skip this step and proceed to Step 2.

  2. Start the Database Engine Tuning Advisor GUI. See Starting Database Engine Tuning Advisor. If you want to view tuning recommendations for an existing tuning session, open it by double-clicking the session name in the Session Monitor window.

    After the new tuning session has finished, or after the tool has loaded the existing session, the Recommendations page is displayed.

  3. On the Recommendations page, click Partition Recommendations and Index Recommendations to view panes that display the tuning session results. If you did not specify partitioning when you set the tuning options for this session, the Partition Recommendations pane is empty.

  4. In either the Partition Recommendations or the Index Recommendations pane, use the scroll bars to view all the information displayed in the grid.

  5. Uncheck Show existing objects at the bottom of the Recommendations tabbed page. This causes the grid to display only those database objects that are referenced in the recommendation. Use the bottom scroll bar to view the right-most column in the recommendations grid, and click an item in the Definition column to view or copy the Transact-SQL script that creates that object in your database.

  6. If you want to save all of the Transact-SQL scripts that create or drop all database objects in this recommendation into one script file, click Save Recommendations on the Actions menu.

To view the tuning summary and reports with the Database Engine Tuning Advisor GUI

  1. Tune a database using the Database Engine Tuning Advisor GUI or the dta command-line utility. See How to: Tune a Database. If you want to use an existing tuning session, skip this step and proceed to step 2.

  2. Start the Database Engine Tuning Advisor GUI. See Starting Database Engine Tuning Advisor. If you want to view tuning summaries and reports for an existing tuning session, open it by double-clicking the session name in the Session Monitor.

  3. After the new tuning session has finished, or after the tool has loaded the existing session, click the Reports tab.

  4. The Tuning Summary pane contains information about the tuning session. The information provided by the Expected percentage improvement and the Space used by recommendation items can be especially useful to decide whether you want to implement the recommendation.

  5. In the Tuning Reports pane, click Select report to choose a tuning report to view. See Choosing a Database Engine Tuning Advisor Report for information about these reports.

To view tuning logs with the Database Engine Tuning Advisor GUI

  1. Tune a database using the Database Engine Tuning Advisor GUI or the dta command-line utility. Make sure that you check Save tuning log on the General tab when you tune the workload. See How to: Tune a Database. If you want to use an existing tuning session, skip this step and proceed to Step 2.

  2. Start the Database Engine Tuning Advisor GUI. See Starting Database Engine Tuning Advisor. If you want to view tuning summaries and reports for an existing tuning session, open it by double-clicking the session name in the Session Monitor window.

  3. After the new tuning session has finished, or after the tool has loaded the existing session, click the Progress tab. The Tuning Log pane displays the contents of the log. The log contains information about workload events that Database Engine Tuning Advisor was unable to analyze. For information about how you can use the tuning log contents to troubleshoot a tuning session, see Troubleshooting Database Engine Tuning Advisor.

    If all events in the tuning session were analyzed by Database Engine Tuning Advisor a message indicating that the tuning log is empty for the session displays. If Save tuning log was not checked on the General tab when the tuning session was originally run, a message displays indicating that.

Security

For important information about the permissions required to use Database Engine Tuning Advisor, see Initializing Database Engine Tuning Advisor.

See Also

Tasks

About the Tuning Log
Errors and Messages

Concepts

Choosing a Database Engine Tuning Advisor Report

Help and Information

Getting SQL Server 2005 Assistance