Using an XML Input File for Tuning
All of the tuning operations that you can perform with the Database Engine Tuning Advisor graphical user interface (GUI) and the dta command-line utility can be defined in the Database Engine Tuning Advisor XML input file. However, the XML input file supports tuning options in addition to those that are available with the GUI and the command-line utility.
The XML input file uses the published Database Engine Tuning Advisor XML schema, which can be found at the following location in your SQL Server 2008 installation directory:
C:\Program Files\Microsoft SQL Server\10\Tools\Binn\schemas\sqlserver\2004\07\dta\dtaschema.xsd
It can also be downloaded from the following URL:
https://schemas.microsoft.com/sqlserver/2004/07/dta
The XML input file lets you use your favorite XML tools when tuning databases, and it provides experienced database administrators with more flexibility. For example, using the XML input file, you can specify a configuration that contains a combination of existing and hypothetical physical design structures (indexes, indexed views, and partitions). Then you can use the dta command-line utility to tune a database as if this combination of existing and hypothetical physical design structures was already implemented. This enables "what-if" analysis, without incurrence of the overhead that accompanies implementation of an actual configuration, before tuning.
The following subsections discuss the tuning operations that you can only perform by using the Database Engine Tuning Advisor XML input file. For more information about this file and how to use it, see XML Input File Reference (Database Engine Tuning Advisor).
Specifying Configurations with the Configuration Element
Although the user-specified configuration feature can be used to a limited extent through the Database Engine Tuning Advisor GUI, this feature is fully supported only when you use the XML input file with the command-line dta utility. When you use the XML input file, you can specify a completely hypothetical configuration in it, or you can specify a configuration that contains a combination of existing and hypothetical physical design structures. Then, after you validate the input file against the Database Engine Tuning Advisor XML schema, you can use the file as input to the dta command-line utility. During the tuning session, Database Engine Tuning Advisor runs the specified workload against the databases. However, Database Engine Tuning Advisor does not evaluate the existing configuration of indexes, indexed views, and partitions. Instead, Database Engine Tuning Advisor uses your configuration that is a combination of hypothetical and existing structures. Use of the hypothetical configuration lets you analyze the effects of a particular configuration on the performance of your database without incurring the overhead of implementing the actual configuration.
To specify a configuration that contains existing and hypothetical physical design structures, use the Configuration subelement after the TuningOptions element in the Database Engine Tuning Advisor XML input file. For more information, see How to: Perform Exploratory Analysis and XML Input File Sample with User-specified Configuration (DTA).
Tuning Inline Workloads with the EventString Element
It is possible to completely avoid use of a workload file when you use XML input with Database Engine Tuning Advisor. Instead, you can specify a workload and its associated weight inline in the XML input file. Avoidance of a separate workload file or table has the following advantages:
You can tune remote servers more easily because you do not need to worry about whether the separate file or table is available to Database Engine Tuning Advisor for tuning.
You can incorporate Database Engine Tuning Advisor functionality more easily into scripts that are portable across your enterprise environment.
To specify an inline workload, use the EventString subelement, for which you can optionally specify an associated weight. When you use this subelement, you specify it for the Workload parent element rather than specifying a separate workload file or table. The following code examples show how use of an EventString element with the XML input file compares to use of a regular workload file with the XML input file:
Examples
A. Specify a Separate Workload File with the Workload Element
<DTAInput>
...code removed
<Workload>
<File>MyWorkload.sql</File>
</Workload>
...code removed
</DTAInput>
B. Specify an Inline Workload with the EventString Element
<DTAInput>
...code removed
<Workload>
<EventString Weight="100">
SELECT * FROM MyTable1
WHERE MyColumn1 > 200
ORDER BY MyColumn1
</EventString>
<EventString Weight="1">
SELECT * FROM MyTable2
WHERE MyColumn2 > 200
ORDER BY MyColumn2
</EventString>
</Workload>
...code removed
</DTAInput>
In the preceding example, different weights were specified for each query in the EventString element: a weight of 100 and a weight of 1. This means that when Database Engine Tuning Advisor tunes these queries the application will treat the query with a weigh of 100 as if there were 100 instances of that query in comparison to one instance of the query with a weight of 1. In the above example, the first query is 100 times more important than the second query for Database Engine Tuning Advisor evaluation purposes. Also note that the greater than sign (>) has been converted to > because > is a reserved character with special meaning in XML.
For an example of specifying an inline workload with the EventString element, see XML Input File Sample with Inline Workload (DTA).
Ignoring Constants in a Workload with the IgnoreConstantsInWorkload Element
Workloads may contain statements that refer to constants. Database Engine Tuning Advisor can use constants in a workload to make recommendations that include indexed views with selection conditions, or range partition functions for partitioned indexes.
However, sometimes it may not be beneficial for Database Engine Tuning Advisor to consider constants in a workload. For example, consider a workload that contains the following statement:
UPDATE BankAccountTable
SET AccountBalance = AccountBalance - 1000.00
WHERE CustomerID =
(SELECT CustomerID FROM Customer WHERE CustomerName = 'Alice')
This workload may include the constant 'Alice' because the workload was captured when Alice made a transaction. If Database Engine Tuning Advisor used this constant, it may not produce an effective tuning recommendation. In this case, it might make sense to specify that Database Engine Tuning Advisor ignore constants when using this workload to tune a database.
The IgnoreConstantsInWorkload element, which resides under the TuningOptions element can be specified in the XML input file to force Database Engine Tuning Advisor to ignore all constants in a workload. When this element is specified, indexed views that Database Engine Tuning Advisor may recommend will not contain selection conditions. In addition, the constants used in partition functions will be derived from data only, and not the constants contained in the workload.
Using a Test Server to Tune a Workload for a Production Server
Tuning a large workload can create significant overhead on the server that is being tuned because of the many calls that Database Engine Tuning Advisor usually makes to the query optimizer during the tuning process. Using a test server in addition to your production server eliminates this problem. Database Engine Tuning Advisor supports this scenario in a unique way:
You make sure that the user who wants to perform tuning exists on both the production and the test servers. If you are a member of the sysadmin fixed server role, this step is unnecessary.
You specify a test server for tuning in the XML input file along with the rest of the parameters that define your tuning session.
You use the dta command-line utility to start the tuning session and begin the workload analysis.
During this test server tuning session, Database Engine Tuning Advisor makes minimal calls to your production server to retrieve information about its hardware profile, database metadata, and statistics to help the query optimizer accurately optimize queries on the test server.
In this scenario, you actually tune the test server, which duplicates the production server environment. After you receive a database design configuration recommendation as a result of tuning your test server, you can then implement it on your production server during a maintenance window. Using this process minimizes the performance impact created by Database Engine Tuning Advisor. In addition, this process saves you the time of actually copying data over from your production server to your test server, and it saves you the expense necessary to duplicate powerful production server hardware in your test environment.
To specify a test server, use the TestServer subelement under the TuningOptions parent element as illustrated in the following example:
Example
<DTAInput>
...code removed
<TuningOptions>
<TestServer>MyTestServer</TestServer>
<FeatureSet>IDX_IV</FeatureSet>
<Partitioning>NONE</Partitioning>
<KeepExisting>NONE</KeepExisting>
</TuningOptions>
...code removed
</DTAInput>
For more information about using this feature and another code example, see Reducing the Production Server Tuning Load.