Tuning Mode Precedence: When the dta Utility Overrides XML Tuning Input
There are multiple ways to specify tuning options by using the dta command prompt utility. You can specify tuning options at the command prompt or in the XML input file that you use with the dta utility. You can also specify tuning options in both places at once if you are re-using an XML input file and want to change the tuning options contained in the file by entering different options at the command prompt. When you specify tuning options at the command prompt and in the XML input file simultaneously, the options that are specified at the command prompt override those that are specified in the XML file.
Example
This example shows the XML input file MyXMLInput.xml, and the syntax used at the command prompt to tune the workload that is specified in the XML input file.
MyXMLInput.xml specifies that Database Engine Tuning Advisor consider the following set of tuning options:
- Indexes only
- No partitioning
- No constraint to keep any existing physical design structures
The syntax used at the command prompt specifies that Database Engine Tuning Advisor consider the following different set of tuning options:
- Indexes and indexed views
- No partitioning
- Constraint to keep all existing physical design structures
When Database Engine Tuning Advisor tunes this workload, the tuning options that are entered at the command prompt override the tuning options that are specified in the XML input file.
Note
If you have specified a configuration of physical design structures with the CONFIGURATION element in the XML input file, the -fk NONE tuning option entered at the command prompt does not override a user-specified configuration in the evaluate mode. For more information, see Configuration Element (DTA).
MyXMLInput.xml
<?xml version="1.0" encoding="utf-16" ?>
<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://schemas.microsoft.com/sqlserver/2004/07/dta">
<DTAInput>
...code removed
<Workload>
<File>MyWorkload.sql</File>
</Workload>
<TuningOptions>
<FeatureSet>IDX</FeatureSet>
<Partitioning>NONE</Partitioning>
<KeepExisting>NONE</KeepExisting>
</TuningOptions>
</DTAInput>
</DTAXML>
dta Utility Command Line Syntax
dta -S MyServer -E -D MyDatabase -ix MyXMLInput.xml -s MySession -fa IDX_IV -fp NONE -fk ALL -ox MyXMLOutput.xml
where -S specifies your server name; -E specifies that Database Engine Tuning Advisor is using a trusted connection; -D specifies the name of the database you want to tune; -ix specifies the XML input file; and -s specifies the tuning session name. The options -fa IDX_IV, -fp NONE, and -fk ALL respectively specify that you want Database Engine Tuning Advisor to consider for its resulting recommendation both indexes and indexed views, no partitioning, and it must keep all existing indexes, indexed views, and partitioning.
See Also
Concepts
Exploratory Analysis Using Database Engine Tuning Advisor
Other Resources
dta Utility
XML Input File Reference (DTA)