XML Input File Sample with Inline Workload (DTA)

  • Article

Copy and paste this sample of an XML input file that specifies a workload with the EventString element into your favorite XML editor or text editor. You can use the EventString element to specify a Transact-SQL script workload in the XML input file instead of using a separate workload file. After copying this sample into your editing tool, replace the values specified for the Server, Database, Schema, Table, Workload, EventString, and TuningOptions 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 (DTA). 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 Weight attributes shown with the following EventString elements are optional. -->
      <EventString Weight="100.01">
        SELECT * FROM MyTableName1 WHERE
        MyColumnName1 &gt; 200
        ORDER BY ColumnName1
      </EventString>
      <EventString Weight="10.02">
        SELECT * FROM MyTableName2 WHERE
        MyColumnName2 BETWEEN 2 AND 25000
        ORDER BY ColumnName2
      </EventString>
    </Workload>
    <TuningOptions>
      <TuningTimeInMin>120</TuningTimeInMin>
      <StorageBoundInMB>1500</StorageBoundInMB>
      <FeatureSet>IDX</FeatureSet>
      <Partitioning>NONE</Partitioning>
      <KeepExisting>NONE</KeepExisting>
      <OnlineIndexOperation>OFF</OnlineIndexOperation>
<!-- The following tuning option specifies the first database to which the Database Engine Tuning Advisor connects when tuning the workload. This element is optional. -->
      <DatabaseToConnect>MyDatabaseName</DatabaseToConnect>
    </TuningOptions>
  </DTAInput>
</DTAXML>

Comments

USE database_name statements can be specified in the inline workload that is contained in the EventString element.

See Also

Reference

XML Input File Reference (DTA)

Other Resources

Using an XML Input File for Tuning
How to: Create XML Input Files
How to: Tune a Database by Using the dta Utility
How to: Perform Exploratory Analysis

Help and Information

Getting SQL Server 2005 Assistance