Edit

View and Work with the Output from the Database Engine Tuning Advisor (DTA)

  • Article

Applies to: SQL Server

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 installation. For information about how to use the Database Tuning Advisor to create workloads and tune a database, see Start and Use the Database Engine Tuning Advisor.

View Tuning Output

The following procedures describe how to view tuning recommendations, summaries, reports, and tuning logs by using the Database Engine Tuning Advisor GUI. For information about user interface options, see User Interface Descriptions later in this topic.

You can also use the GUI to view tuning output that is generated by the dta command-line utility.

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 Use 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. For more information, see Start and Use the Database Engine Tuning Advisor. 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. For more information, see Start and Use the 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. For more information, see Start and Use the Database Engine Tuning Advisor. 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. For more information, see Start and Use the 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.

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. 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. For more information, see Start and Use the 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.

    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.

Implement Tuning Recommendations

You can implement the Database Engine Tuning Advisor recommendations manually or automatically as part of the tuning session. If you want to examine the tuning results first before implementing them, use the Database Engine Tuning Advisor GUI. You can then use SQL Server Management Studio to manually run the Transact-SQL scripts that Database Engine Tuning Advisor generates as a result of analyzing a workload to implement the recommendations. If you do not need to examine the results before implementing them, you can use the -a option with the dta command prompt utility. This causes the utility to automatically implement the tuning recommendations after it analyzes your workload. The following procedures explain how to use both Database Engine Tuning Advisor interfaces to implement tuning recommendations.

To manually implement tuning recommendations with the Database Engine Tuning Advisor GUI

  1. Tune a database using the Database Engine Tuning Advisor GUI or the dta command prompt utility. For more information, see Start and Use the Database Engine Tuning Advisor. 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. For more information, see Start and Use the Database Engine Tuning Advisor. If you want to implement tuning recommendations for an existing tuning session, open it by double-clicking the session name in Session Monitor.

  3. After the new tuning session has finished, or after the tool has loaded the existing session, click Apply Recommendations on the Actions menu.

  4. In the Apply Recommendations dialog box choose from Apply now or Schedule for later. If you choose Schedule for later, select the appropriate date and time.

  5. Click OK to apply the recommendations.

To automatically implement tuning recommendations using the dta command prompt utility

  1. Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis.

    Keep the following considerations in mind before you begin tuning:

    • When using a trace table as a workload, that table must exist on the same server where Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, move it to the server where Database Engine Tuning Advisor is tuning.

    • If a tuning session continues running longer than you had anticipated it would run, you can press CTRL+C to end the tuning session. Pressing CTRL+C under these circumstances forces dta to produce the best recommendation possible based on how much of the workload it has consumed, and does not waste the time that the tool has already used to tune the workload.

  2. From a command prompt, enter the following:

    dta -E -D DatabaseName -if WorkloadFile -s SessionName -a

    where -E specifies that your tuning session uses a trusted connection (instead of a login ID and password), -D specifies the name of the database you want to tune or a comma-delimited list of multiple databases that the workload uses, -if specifies the name and path to a workload file, -s specifies a name for your tuning session, and -a specifies that you want the dta command prompt utility to automatically apply the tuning recommendations after the workload is analyzed without prompting you. For more information about using the dta command prompt utility to tune databases, see Start and Use the Database Engine Tuning Advisor.

  3. Press ENTER.

Perform Exploratory Analysis

The user-specified configuration feature of Database Engine Tuning Advisor enables database administrators to perform exploratory analysis. Using this feature, database administrators specify a desired physical database design to Database Engine Tuning Advisor, and then they can evaluate the performance effects of that design without implementing it. User-specified configuration is supported by both the Database Engine Tuning Advisor graphical user interface (GUI) and the command-line utility. However, the command-line utility provides the greatest flexibility.

If you use the Database Engine Tuning Advisor GUI, you can evaluate the effects of implementing a subset of a Database Engine Tuning Advisor tuning recommendation, but you cannot add hypothetical physical design structures for Database Engine Tuning Advisor to evaluate.

The following procedures explain how to use the user-specified configuration feature with both tool interfaces.

Using Database Engine Tuning Advisor GUI to Evaluate Tuning Recommendations

The following procedure describes how to evaluate a recommendation that is generated by Database Engine Tuning Advisor, but the GUI does not enable you to specify new physical design structures for evaluation.

To evaluate tuning recommendations with the Database Engine Tuning Advisor GUI

  1. Use the Database Engine Tuning Advisor GUI to tune a database. For more information, see Start and Use the Database Engine Tuning Advisor. If you want to evaluate an existing tuning session, double-click it in Session Monitor.

  2. On the Recommendations tab, clear the recommended physical design structures that you do not want to use.

  3. On the Actions menu, click Evaluate Recommendations. A new tuning session is created for you.

  4. Type the new Session name. To view the physical database design structure configuration that you are evaluating, choose Click here to see the configuration section, in the Description area at the bottom of the Database Engine Tuning Advisor application window.

  5. Click the Start Analysis button on the toolbar. When Database Engine Tuning Advisor is finished, you can view the results on the Recommendations tab.

Using Database Engine Tuning Advisor GUI to Export Tuning Session Results for "What-if" Tuning Analysis

The following procedure describes how to export Database Engine Tuning Advisor tuning session results to an XML file, which you can edit, and then tune it with the dta command-line utility. This enables you to perform tuning analysis on hypothetical new physical design structures without incurring the overhead of implementing them in your database before you find out whether they produce the performance improvements that you need. Using the Database Engine Tuning Advisor GUI to initially tune your database and then exporting the tuning results to an .xml file is a good way for users who are new to XML to use the flexibility of the Database Engine Tuning Advisor XML schema to perform "what-if" analysis.

To export tuning session results from the Database Engine Tuning Advisor GUI for "what-if" analysis with the dta command-line utility

  1. Use the Database Engine Tuning Advisor GUI to tune a database. For more information, see Start and Use the Database Engine Tuning Advisor. If you want to evaluate an existing tuning session, double-click it in the Session Monitor.

  2. On the File menu, click Export Session Results and save it as an XML file.

  3. Open the XML file created in Step 2 in your favorite XML editor, text editor, or in SQL Server Management Studio. Scroll down to the Configuration element. Copy and paste the Configuration element section into an XML input file template after the TuningOptions element. Save this XML input file.

  4. In the new XML input file that you created in Step 3, specify any tuning options you want in the TuningOptions element, edit the Configuration element section (add or delete the physical design structures as appropriate for your analysis), save the file, and validate it against the Database Engine Tuning Advisor XML schema. For information about editing this XML file, see XML Input File Reference (Database Engine Tuning Advisor).

  5. Use the XML file that you created in Step 4 as input to the dta command line utility. For information about using XML input files with this tool, see the section "Tune a Database by Using the dta Utility" in Start and Use the Database Engine Tuning Advisor.

Using the User-specified Configuration Feature with the dta Command Line Utility

If you are an experienced XML developer, you can create a Database Engine Tuning Advisor XML input file in which you can specify a workload and a hypothetical configuration of physical database design structures, such as indexes, indexed views, or partitioning. Then you can use the dta command-line utility to analyze the effects this hypothetical configuration has on query performance for your database. The following procedure explains this process step by step:

To use the user-specified configuration feature with the dta command line utility

  1. Create a tuning workload. For information about performing this task, see Start and Use the Database Engine Tuning Advisor.

  2. Copy and paste the XML Input File Sample with User-specified Configuration (DTA) into your XML editor or a text editor. Use this sample to create an XML input file for your tuning session. For information about performing this task, see the section "Create XML Input Files" in Start and Use the Database Engine Tuning Advisor.

  3. Edit the TuningOptions and the Configuration elements in the sample XML input file. In the TuningOptions element, specify what physical design structures you want Database Engine Tuning Advisor to consider during the tuning session. In the Configuration element, specify the physical design structures that match the hypothetical configuration of physical database design structures that you want Database Engine Tuning Advisor to analyze. For information about what attributes and child elements you can use with the TuningOptions and the Configuration parent elements, see XML Input File Reference (Database Engine Tuning Advisor).

  4. Save the input file with an .xml extension.

  5. Validate the XML input file you saved in Step 4 against the Database Engine Tuning Advisor XML schema. This schema is installed at the following location when you install Microsoft SQL Server:

    C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\dta\dtaschema.xsd

    The Database Engine Tuning Advisor XML schema is also available online at https://schemas.microsoft.com/sqlserver/2004/07/dta/.

  6. After creating a workload and an XML input file, you are ready to submit the input file to the dta command-line utility for analysis. Make sure that you specify an XML output file name for the -ox utility argument. This creates an XML output file with a recommended configuration specified in the Configuration element. If you want to run Database Engine Tuning Advisor again to check another hypothetical configuration that is based on the output, you can copy and paste the Configuration element contents from the output file into a new or your original XML input file. For information about using an XML input file with the dta utility, see the section "Tune a Database by Using the dta Utility" in Start and Use the Database Engine Tuning Advisor.

    After tuning is finished, either use the Database Engine Tuning Advisor GUI to view the tuning reports, or open the XML output file to view the TuningSummary and the Configuration elements to view the Database Engine Tuning Advisor recommendations. For information about viewing the results of your tuning session, see View Tuning Output earlier in this topic. Also note that the XML output file may contain Database Engine Tuning Advisor analysis reports.

  7. Repeat steps 6 and 7 until you create the hypothetical configuration that produces the query performance improvement that you need. Then you can implement the new configuration. For more information, see Implement Tuning Recommendations earlier in this topic.

Review, Evaluate, and Clone Tuning Sessions

The Database Engine Tuning Advisor creates a new tuning session each time that you start analyzing the effects of a workload on your database or databases. You can use the Session Monitor in the Database Engine Tuning Advisor GUI to view or reload all tuning sessions that have run on a given instance of Microsoft SQL Server. Having all the existing tuning sessions available to review makes it easy to: clone sessions based on existing ones, edit existing tuning recommendations and then use Database Engine Tuning Advisor to evaluate the edited session, or perform tuning at regular intervals to monitor the physical design of your databases. For example, you may decide to tune database on a monthly schedule.

Before you can review any tuning sessions for an instance of SQL Server, you must create tuning sessions on the server instance by tuning workloads with the Database Engine Tuning Advisor. For more information, see Start and Use the Database Engine Tuning Advisor.

Review Existing Tuning Sessions

Use the following steps to browse the existing tuning sessions on a given instance of SQL Server.

To review existing tuning sessions

  1. Start the Database Engine Tuning Advisor GUI. For more information, see Start and Use the Database Engine Tuning Advisor.

  2. All existing tuning sessions are displayed in the upper half of the Session Monitor window. The number of sessions displayed depends on how many times you have tuned databases on this SQL Server instance. Use the scroll bars to view all tuning sessions.

  3. Click a tuning session name once and its details appear in the lower half of the Session Monitor window.

  4. Double-click a tuning session name and its information is loaded into Database Engine Tuning Advisor. After the session information loads, you can choose any of the tabs to view information about this tuning session.

Evaluate Existing Tuning Sessions As Hypothetical Configurations

Use the following steps to evaluate an existing tuning session. Evaluating an existing tuning session involves viewing and editing its recommendations, and then re-tuning. For example, you decide that you want to only create indexes on table1, so you delete the creation of indexed views and partitioning from an existing tuning recommendation. Then Database Engine Tuning Advisor creates a new tuning session and tunes the workload against your databases using the edited recommendations as a hypothetical configuration. This means that Database Engine Tuning Advisor tunes the workload against the databases as if the edited recommendations have been implemented, enabling you to perform limited "what-if" analysis. It is limited what-if analysis because you can only choose a subset of an existing recommendation when you use the Database Engine Tuning Advisor GUI. To perform full what-if analysis, specifying a completely new hypothetical configuration that is not a subset of any previous tuning session, you must use the Database Engine Tuning Advisor XML input file with the dta command-line utility.

To evaluate an existing tuning session

  1. After starting Database Engine Tuning Advisor, double-click a tuning session in the upper half of the Session Monitor, which loads the session information into Database Engine Tuning Advisor.

  2. Click the Progress tab to check the tuning log, which contains error information about any events in the workload that Database Engine Tuning Advisor could not tune. This information can help you evaluate the effectiveness of the workload.

  3. If you would like to review the tuning results for this session further, click the Reports tab. There you can view the tuning summary or choose a tuning report from the Select report list.

  4. Click the Recommendations tab to view the tuning recommendations.

  5. If there are any recommendations that you are unsure about implementing, uncheck them.

  6. On the Actions menu, click Evaluate Recommendations. Database Engine Tuning Advisor creates a new tuning session that uses the edited recommendation as a hypothetical configuration. To view the hypothetical configuration in XML, choose Click here to see the configuration section.

  7. On the General tab, type a Session name, and make sure the correct Workload is specified.

  8. On the Tuning Options tab, you can specify a tuning time, or any of the Advanced Options.

  9. Click the Start Analysis button on the toolbar. Database Engine Tuning Advisor starts tuning the databases using the hypothetical configuration. When Database Engine Tuning Advisor finishes, you can view the results of this session as you normally would for any session.

Clone Existing Tuning Sessions

You can create new tuning sessions based on existing sessions by choosing the cloning option in Database Engine Tuning Advisor. When you use the cloning option, you base a new tuning session on an existing session. Then you can change the tuning options for the new session as needed. When you evaluate an existing session as described in the previous procedure, Database Engine Tuning Advisor also creates a new tuning session, but you cannot change the tuning options.

To create new tuning sessions by cloning existing sessions

  1. After starting Database Engine Tuning Advisor, double-click a tuning session in the upper half of the Session Monitor, which loads the session information into Database Engine Tuning Advisor.

  2. On the Actions menu, click Clone Session.

  3. On the General tab, type a Session name, and make sure the correct Workload is specified.

  4. On the Tuning Options tab, you can specify a tuning time, the physical design structures Database Engine Tuning Advisor should consider creating, and what it should consider dropping in its recommendation.

  5. Click Advanced Options if you want to set a space limit for recommendations, a maximum number of columns per index, and whether you want Database Engine Tuning Advisor to generate recommendations that can be implemented while SQL Server is online.

  6. Click the Start Analysis button on the toolbar to analyze the effects of the workload like any other tuning session. When Database Engine Tuning Advisor finishes, you can view the results of this session as you normally would for any session.

User Interface Descriptions

Sessions Monitor

Session Monitor displays information about sessions that are opened in the Database Engine Tuning Advisor. To display information about the session in the property window, select a session name in Session Monitor.

Recommendations Tab

The Recommendations tab appears after Database Engine Tuning Advisor completes analysis of a workload. This grid contains the recommendations for each object considered. Partition recommendations, if any, are presented in the upper grid, and the index recommendations are presented in the lower grid. The grids do not appear if there are no recommendations.

The Definition column contains the definition of the recommended partition or index as a hyperlink. This column is usually too narrow to see the entire definition. Click the hyperlink to display a dialog box containing the full definition and the Copy to Clipboard button.

Partition Recommendations

Database Name
The database containing the objects recommended to be modified.

Recommendation
The action recommended to improve performance. Possible values are Create and Drop.

Target of Recommendation
The partition function or scheme affected by the recommendation. The icon in this column reflects the recommendation to drop or add the Target of Recommendation and whether it is a partition function or scheme.

Details
A description of the Target of Recommendation. Possible values include a range for partition functions, or blank for partition schemes.

No. of Partitions
The number of partitions defined by the recommended partitioning functions. When this function is used with a scheme and then applied to a table, the data in the table is divided into that many partitions.

Definition
The definition of the Target of Recommendation. Click the column to open the SQL Script Preview dialog box, with a script for the recommended action.

Index Recommendations

Database Name
The database containing the objects recommended to be modified.

Object Name
The table related to the recommendation.

Recommendation
The action recommended to improve performance. Possible values are Create and Drop.

Target of Recommendation
The index or view affected by the recommendation. The icon in this column reflects the recommendation to drop or add the Target of Recommendation.

Details
A description of the Target of Recommendation. Possible values include clustered, indexed view, or blank indicating a nonclustered index. Also indicates whether the index is unique.

Partition Scheme
The partition scheme is provided in this column if partitioning is recommended.

Size (KB)
The expected size of the new object that is being recommended. If this column is blank, click See Reports for sizes of existing objects.

Definition
The definition of the Target of Recommendation. Click the column to open the SQL Script Preview dialog box, with a script for the recommended action.

Show existing objects
Select to show all existing objects in the grid, even if no recommendations related to the objects are made by Database Engine Tuning Advisor.

See Reports for sizes of existing objects
Select to view reports that provide the size of existing objects in the recommendations grid.

Actions Menu/Apply Recommendations Options

After a workload has been analyzed and recommendations have been presented, on the Actions menu, click Apply Recommendations to open the Apply Recommendations dialog box.

Apply now
Generate a script for the recommendations, and runs the script to implement the recommendations.

Schedule for later
Generate a script for the recommendations, and save the actions as a SQL Server Agent job.

Date
Specify the date you want to run the SQL Server Agent job to apply the recommendations.

Time
Specify the time you want to run the SQL Server Agent job to apply the recommendations.

Reports Tab Options

The Reports tab appears after Database Engine Tuning Advisor completes analysis of a workload.

Tuning Summary
Displays a summary of the Database Engine Tuning Advisor recommendations.

Date
The date that Database Engine Tuning Advisor created the report.

Time
The time that Database Engine Tuning Advisor created the report.

Server
The server that was the target of the Database Engine Tuning Advisor workload.

Databases to tune
The database affected by Database Engine Tuning Advisor recommendations.

Workload file
Appears when the workload is a file.

Workload table
Appears when the workload is a SQL Server table.

Workload
Appears when the workload has been imported from the Query Editor in SQL Server Management Studio.

Maximum tuning time
The maximum time configured to be available for the Database Engine Tuning Advisor analysis.

Time taken for tuning
The time actually used by Database Engine Tuning Advisor to analyze the workload.

Expected percentage improvement
The percentage improvement expected with the target workload if all Database Engine Tuning Advisor recommendations are implemented.

Maximum space for recommendation (MB)
The maximum space considered for the recommendations. This value is configured before the analysis is made, using the Advanced Options button, on the Tuning Options tab.

Space used currently (MB)
The space currently used by indexes in the database analyzed.

Space used by recommendation (MB)
The approximate space expected to be used by indexes if all the Database Engine Tuning Advisor recommendations are implemented.

Number of events in workload
Number of events contained in the workload.

Number of events tuned
Number of events in the workload that were tuned. If an event cannot be tuned, it is listed in the tuning log, which is available on the Progress tab.

Number of statements tuned
Number of statements in the workload that were tuned. If a statement cannot be tuned, it is listed in the tuning log which is available on the Progress tab.

Percent SELECT statements in the tuned set
Percentage of tuned statements that are SELECT statements. Only appears if there are SELECT statements that have been tuned.

Percent UPDATE statements in the tuned set
Percentage of tuned statements that are UPDATE statements. Only appears if there are UPDATE statements that have been tuned.

Number of indexes recommended to be [created | dropped]
Recommended number of indexes to be created or dropped on the tuned database. Only appears if indexes are part of the recommendation.

Number of indexes on views recommended to be [created | dropped]
Recommended number of indexes on views to be created or dropped on the tuned database. Only appears if indexes on views are part of the recommendation.

Number of statistics recommended to be created
Recommended number of statistics to be created on the tuned database. Only appears if statistics are recommended.

Select Report
See the details of the selected report. The columns in the grid vary with each report.

See Also

Start and Use the Database Engine Tuning Advisor
dta Utility