How to: 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 graphical user interface (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 Database Engine Tuning Advisor. See How to: Tune a Database.
Reviewing 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
Start the Database Engine Tuning Advisor GUI. See Starting Database Engine Tuning Advisor.
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.
Click a tuning session name once and its details appear in the lower half of the Session Monitor window.
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.
Evaluating 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. See How to: Perform Exploratory Analysis.
To evaluate an existing tuning session
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.
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.
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.
Click the Recommendations tab to view the tuning recommendations.
If there are any recommendations that you are unsure about implementing, uncheck them.
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.
On the General tab, type a Session name, and make sure the correct Workload is specified.
On the Tuning Options tab, you can specify a tuning time, or any of the Advanced Options.
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.
Cloning 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
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.
On the Actions menu, click Clone Session.
On the General tab, type a Session name, and make sure the correct Workload is specified.
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.
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.
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.
See Also
Tasks
Concepts
Using Session Monitor to Evaluate Tuning Recommendations
Other Resources
Database Engine Tuning Advisor Tutorial