• Article

Understanding Tuning Progress

You can monitor tuning progress in both the Database Engine Tuning Advisor graphical user interface (GUI) and the dta command prompt utility. Monitoring tuning progress can help you determine whether you are using an effective workload, and it can help you spot problems before significant time is wasted. The following sections explain how to monitor the workload in both user interfaces. This topic also explains the order in which Database Engine Tuning Advisor tunes events when various types of workloads are used.

Monitoring Tuning Progress with the Graphical User Interface

When you start analyzing a workload with the Database Engine Tuning Advisor graphical user interface (GUI), the application automatically displays tuning progress on the Progress tab. This tabbed page consists of three panes. The top pane displays an animated graphic and values that change as the tuning session progresses. The middle pane contains details about the progress of tuning. The bottom pane displays the tuning log, which refreshes periodically as tuning progresses so you can monitor the session.

If you want to stop Database Engine Tuning Advisor before a workload is fully processed, click Stop Analysis on the toolbar. When a session is stopped, Database Engine Tuning Advisor generates the best recommendation possible for the amount of workload consumed.

Note

Tuning involves more than consumption of the workload. After Database Engine Tuning Advisor consumes the workload, it then analyzes the queries and generates the recommendation and reports. Tuning can be an expensive process and may take additional time. The only way to be sure that 100 percent of the workload is consumed and that Database Engine Tuning Advisor has enough time to generate its recommendation and reports is to specify an unlimited tuning time. To specify an unlimited tuning time, clear Limit tuning time on the Tuning Options tab.

Monitoring Tuning Progress from the Command Line

The dta command prompt utility reports two key measures of progress. By default, dta writes the following information to your screen while it tunes workloads:

  • Workload processed

    This is the fraction of all events in the workload that Database Engine Tuning Advisor has consumed thus far. If tuning is stopped at any point, this is the fraction of the workload upon which Database Engine Tuning Advisor makes its recommendation. If Database Engine Tuning Advisor is allowed to process more of the workload, its recommendation may change.

    When you specify an unlimited tuning time, tuning finishes only when Workload processed reaches 100 percent. Be aware that tuning involves more than consuming the workload. After Database Engine Tuning Advisor consumes the workload, it then analyzes the queries and generates the recommendation and reports. Tuning can be an expensive process and may take additional time. The only way to be sure that 100 percent of the workload is consumed and that Database Engine Tuning Advisor has enough time to generate its recommendation and reports is to specify an unlimited tuning time. To specify an unlimited tuning time, set the -A option to 0.

  • Estimated improvement

At any point during the tuning, this number represents the estimated performance improvement if you implement the recommendation that Database Engine Tuning Advisor has generated for the workload processed so far.

If you want to stop Database Engine Tuning Advisor before a workload is fully processed, press CTRL+C.

Event Tuning Order

Events in the workload can be tuned either in the order they appear in the workload (sequentially), or in the order of their duration. If the workload is a Transact-SQL script file, events are tuned sequentially. Similarly, if the workload is a trace file or trace table, and the trace does not contain the Duration column, Database Engine Tuning Advisor tunes the events sequentially.

If the trace file or trace table contains the Duration column, Database Engine Tuning Advisor tunes the events in the descending order of the Duration value so that the longer executing queries are tuned first.