Limiting Tuning Duration and Events
You can control Database Engine Tuning Advisor tuning sessions either through limits on the time that the application tunes or limits on the number of events that it tunes. Limits on the tuning analysis duration are useful when you need to tune a production server during a maintenance window. Limits on the number of events tuned are useful when you compare two hypothetical configurations by tuning the same workload for each.
Database Engine Tuning Advisor may not tune all events in a workload for any given configuration. If you specify the number of events to be tuned, you ensure that the tuning session for each configuration considers the same number of tunable events so that the results are more comparable.
Controlling Duration of Tuning
Database Engine Tuning Advisor analyzes a workload until it finds the optimal set of physical performance structures possible for the specified databases. The amount of time this takes depends on the length of the workload. Database Engine Tuning Advisor could take several days to complete a thorough analysis on a workload that contains hundreds of events. Although the databases are available throughout the analysis, performance may be affected.
To control how long Database Engine Tuning Advisor analyzes a workload, set the maximum time. The maximum time is the number of hours and minutes that you want Database Engine Tuning Advisor to spend on analysis of a workload. The result of a maximum time setting is a recommendation that is based on the best solution that Database Engine Tuning Advisor can produce before the time expires.
In the Database Engine Tuning Advisor graphical user interface (GUI), this parameter is set to 1 hour by default. In the dta command-line utility, the default limit is set to 8 hours. If complete analysis requires less time than the set limit, tuning terminates upon completion. You can specify an unlimited tuning time to generate the best recommendation possible in both tool interfaces.
The following subsections describe how to set this tuning option in the GUI and at the command prompt.
Set the Tuning Time in the Graphical User Interface
You set the tuning time limit in the Database Engine Tuning Advisor GUI by selecting Limit tuning time on the Tuning Options tab, and then setting the Stop at date and time.
The following details apply:
In general, when you specify a longer tuning time, you get better overall results because more of the workload can be analyzed. The recommendations are based on more complete information.
By default, the maximum tuning limit is set to 1 hour.
To specify an unlimited tuning time, clear the Limit tuning time check box.
Set the Tuning Time in the dta Command Line Utility
You can set the tuning time limit through the dta command-line utility by specifying a value for the -A argument. For example, examine the following dta command:
dta -E -s MySession -D AdventureWorks2008R2 -if MyWorkloadScript.sql -fa IDX -A 120
where -E specifies a trusted connection, -D specifies the database to tune, -if specifies the workload file, -fa IDX specifies that Database Engine Tuning Advisor should only consider adding indexes in its recommendation, and -A 120 means that tuning must be completed within 120 minutes.
When running the dta utility from the command prompt (rather than calling it in a script for automatic tuning), press CTRL+C to stop the tuning. Database Engine Tuning Advisor then returns the best recommendation possible based on the amount of workload that has been consumed at that time.
The following details apply:
In general, when you specify a longer tuning time, you get better overall results because more of the workload can be analyzed. Thus, the recommendations are based on fuller information.
To specify tuning with an unlimited amount of time, use the command-line argument –A 0. Use this argument to obtain the best recommendation for the entire workload.
If the –A option is not specified, it defaults to –A 480 (an 8-hour tuning time).
Even if you press CTRL+C, the dta utility may take some time to populate the analysis reports before it exits.
Set the Tuning Time in the XML Input File
You can also set a maximum tuning time limit in the XML input file that can be used with the dta command-line utility. To specify a tuning time limit in minutes in the XML input file, use the TuningTimeInMin subelement under the TuningOptions parent element. For more information about the XML input file, see XML Input File Reference (Database Engine Tuning Advisor).
Controlling the Number of Events to Tune
You can control the number of events in workloads that are tuned by Database Engine Tuning Advisor. This option is only available from the dta command-line utility, by using the –n argument. The behavior of the dta utility when the -n argument is used depends on the value specified for the -A argument, which specifies the tuning time limit, as follows:
dta stops when it has tuned the specified number of event or the specified tuning time limit has elapsed, whichever occurs first. If the -n argument is not specified, by default dta tunes all events in the workload. If the -A argument is not specified, dta assumes a tuning time limit of 8 hours by default.
To make sure that the number of events specified with the -n argument are tuned, also specify 0 for the -A argument, which specifies an unlimited tuning time. For example, -n 250 -A 0.
Specify the Number of Events to Tune in the XML Input File
You can also specify a number of events to tune in the XML input file that can be used with the dta command-line utility. To specify a number of events to tune in the XML input file, use the NumberOfEvents subelement under the TuningOptions parent element. For more information about the XML input file, see XML Input File Reference (Database Engine Tuning Advisor).
Best Practices for Limiting the Time and Number of Events Tuned
Following are suggestions for limiting the time and number of events tuned:
For single-query or small workloads (fewer than 100 events), specify an unlimited tuning time. A specification of unlimited tuning time results in the best recommendations by Database Engine Tuning Advisor and in most cases tuning will complete in a relatively short time.
For large workloads (more than 100 events), consider the following options in the order they are listed. Resort to the last option (4) only after first considering options 1 through 3.
If the user has a time constraint, limit the tuning time.
If tuning a fixed number of events is sufficient (for example, the first 10,000 events may be representative of the rest of the workload), use the dta command-line utility and specify the number of events using the –n argument.
If you are using the dta command-line utility and want to limit the tuning time further, you can use both the –A and –n arguments. For example, specifying -A 240 and –n 1000 causes Database Engine Tuning Advisor to stop tuning as soon as it has tuned 1000 events or four hours have elapsed, whichever happens first.
The time taken for tuning depends on the complexity of queries (number of tables referenced), feature sets chosen (tuning indexed views is more time consuming than tuning indexes), and size of data (for statistics creation). In most cases, the majority of time that Database Engine Tuning Advisor spends tuning is in making calls to the query optimizer. A simple rule of thumb for how much time may be appropriate to give Database Engine Tuning Advisor for tuning is as follows:
For simple queries that reference one to three tables, allow about 1 second per query if tuning indexes only and about 10 seconds per query if tuning indexes and indexed views. For complex queries that reference more than three tables, allow about 10 seconds per query if tuning indexes only and about 100 seconds per query if tuning indexes and indexed views.
If Database Engine Tuning Advisor indicates that 100% of the workload was consumed, it means that the entire workload was parsed, but not necessarily tuned. To determine whether the entire workload was tuned, search the end of the tuning log for the following message:
"All events in the workload were not tuned. Consider increasing time bound or specify number of events to be considered in the input XML."
If such a message is present in the tuning log, Database Engine Tuning Advisor was unable to tune the entire workload. To resolve this, specify a longer tuning time. To ensure that all events in the workload are tuned, you can specify an unlimited tuning time. If you choose not to specify an unlimited tuning time, Database Engine Tuning Advisor attempts to tune the largest number of events possible within the tuning time specified.
Note There is no direct mapping between the Fast, Medium, or Thorough modes of the in Microsoft SQL Server 2000 Index Tuning Wizard to the –A and –n arguments in the Database Engine Tuning Advisor. In general, if tuning in a particular mode (Fast, Medium, or Thorough) in SQL Server 2000 took a certain amount of time, the same amount of time typically provides comparable or better recommendations in SQL Server 2005 Database Engine Tuning Advisor. It is recommended that users of the Thorough mode use Database Engine Tuning Advisor with an unlimited tuning time and an unlimited number of events to be tuned in the workload.
Additional Methods of Limiting Tuning Duration
In addition to limiting the Database Engine Tuning Advisor execution time, you can tune only a subset of the tables in the database.
The following options are also available:
Keep all existing physical design structures (PDS).
Keep indexes only.
Keep clustered indexes only.
The Database Engine Tuning Advisor includes any index hint or query hint in the final recommendation, even if the index is not optimal for the table. Indexes on other tables referenced in the query can be proposed and recommended. However, all indexes that are specified as hints will always be part of the final recommendation. Hints can prevent Database Engine Tuning Advisor from choosing a better execution plan. Consider removing any index hint from queries before analysis of the workload.