Share via

  • Article

XML Input File Sample with User-specified Configuration (DTA)

Copy and paste this sample of an XML input file that specifies a user-specified configuration with the Configuration element into your favorite XML editor or text editor. This enables you to perform "what-if" analysis. "What-if" analysis involves using the Configuration element to specify a set of hypothetical physical design structures for the database you want to tune. Then you use Database Engine Tuning Advisor to analyze the effects of running a workload against this hypothetical configuration to find out whether it improves query processing performance. This type of analysis provides the advantage of evaluating the new configuration without incurring the overhead of actually implementing it. If your hypothetical configuration does not provide the performance improvements you want, it is easy to change it and analyze it again until you reach the configuration that produces the results you need.

After copying this sample into your editing tool, replace the values specified for the Server, Database, Schema, Table, Workload, TuningOptions, and Configuration elements with those for your specific tuning session. For more information about all of the attributes and child elements that you can use with these elements, see the XML Input File Reference (Database Engine Tuning Advisor). The following sample uses only a subset of available attribute and child element options.

Code

<?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>
    <Server>
      <Name>MyServerName</Name>
<!-- To tune multiple databases, list them and their associated tables in the following section. -->
      <Database>
        <Name>MyDatabaseName</Name>
        <Schema>
          <Name>MyDatabaseSchemaName</Name>
<!-- You can list as many tables as necessary in the following section. -->
          <Table>
            <Name>MyTableName1</Name>
          </Table>
          <Table>
            <Name>MyTableName2</Name>
          </Table>
        </Schema>
      </Database>
    </Server>
    <Workload>
<!-- The following element specifies a workload file, which can be a trace file or a Transact-SQL script file. -->
      <File>c:\PathToYourWorkloadFile</File>
    </Workload>
    <TuningOptions>
      <TuningTimeInMin>180</TuningTimeInMin>
      <StorageBoundInMB>10000</StorageBoundInMB>
      <FeatureSet>IDX_IV</FeatureSet>
      <Partitioning>NONE</Partitioning>
      <KeepExisting>NONE</KeepExisting>
      <OnlineIndexOperation>OFF</OnlineIndexOperation>
    </TuningOptions>
    <Configuration SpecificationMode="Absolute">
      <Server>
        <Name>MyServerName</Name>
          <Database>
            <Name>MyDatabaseName</Name>
            <Schema>
              <Name>MyDatabaseSchemaName</Name>
                <Table>
                  <Name>MyTableName1</Name>
                  <Recommendation>
                    <Create>
                      <Index Clustered="true" Unique="false" Online="false" IndexSizeInMB="873.75">
                        <Name>MyIndexName</Name>
                        <Column Type="KeyColumn" SortOrder="Ascending">
                          <Name>MyColumnName1</Name>
                        </Column>
                        <Filegroup>MyFileGroupName1</FileGroup>
                      </Index>
                    </Create>
                  </Recommendation>
                </Table>
            </Schema>
          </Database>
      </Server>
    </Configuration>
  </DTAInput>
</DTAXML>

Comments

  • Dropping physical design structures is not supported if you specify the Absolute mode for the Configuration element (Configuration SpecificationMode="Absolute").

  • You cannot create and drop the same physical design structure in either mode (Relative or Absolute) of the Configuration element.