Using an XML Input File for Tuning

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

<DTAInput>
...code removed
  <Workload>
    <File>MyWorkload.sql</File>
  </Workload>
...code removed
</DTAInput>

<DTAInput>
...code removed
  <Workload>
    <EventString Weight="100">
     SELECT * FROM MyTable1
     WHERE MyColumn1 &gt; 200
     ORDER BY MyColumn1
    </EventString>
    <EventString Weight="1">
     SELECT * FROM MyTable2
     WHERE MyColumn2 &gt; 200
     ORDER BY MyColumn2
    </EventString>
  </Workload>
...code removed
</DTAInput>

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:

1. 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.

2. You specify a test server for tuning in the XML input file along with the rest of the parameters that define your tuning session.

3. 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>

See Also

Other Resources