Share via

  • Article

How to: Perform Exploratory Analysis

The user-specified configuration feature of Database Engine Tuning Advisor enables database administrators to perform exploratory analysis. Using this feature, database administrators specify a desired physical database design to Database Engine Tuning Advisor, and then they can evaluate the performance effects of that design without implementing it. User-specified configuration is supported by both the Database Engine Tuning Advisor graphical user interface (GUI) and the command-line utility. However, the command-line utility provides the greatest flexibility.

If you use the Database Engine Tuning Advisor GUI, you can evaluate the effects of implementing a subset of a Database Engine Tuning Advisor tuning recommendation, but you cannot add hypothetical physical design structures for Database Engine Tuning Advisor to evaluate.

For more information about the user-specified configuration feature, see Exploratory Analysis Using Database Engine Tuning Advisor.

The following procedures explain how to use the user-specified configuration feature with both tool interfaces.

Using Database Engine Tuning Advisor GUI to Evaluate Tuning Recommendations

The following procedure describes how to evaluate a recommendation that is generated by Database Engine Tuning Advisor, but the GUI does not enable you to specify new physical design structures for evaluation.

To evaluate tuning recommendations with the Database Engine Tuning Advisor GUI

  1. Use the Database Engine Tuning Advisor GUI to tune a database. (See How to: Tune a Database by Using Database Engine Tuning Advisor.) If you want to evaluate an existing tuning session, double-click it in Session Monitor.

  2. On the Recommendations tab, clear the recommended physical design structures that you do not want to use.

  3. On the Actions menu, click Evaluate Recommendations. A new tuning session is created for you.

  4. Type the new Session name. To view the physical database design structure configuration that you are evaluating, choose Click here to see the configuration section, in the Description area at the bottom of the Database Engine Tuning Advisor application window.

  5. Click the Start Analysis button on the toolbar. When Database Engine Tuning Advisor is finished, you can view the results on the Recommendations tab.

Using Database Engine Tuning Advisor GUI to Export Tuning Session Results for "What-if" Tuning Analysis

The following procedure describes how to export Database Engine Tuning Advisor tuning session results to an XML file, which you can edit, and then tune it with the dta command-line utility. This enables you to perform tuning analysis on hypothetical new physical design structures without incurring the overhead of implementing them in your database before you find out whether they produce the performance improvements that you need. Using the Database Engine Tuning Advisor GUI to initially tune your database and then exporting the tuning results to an .xml file is a good way for users who are new to XML to use the flexibility of the Database Engine Tuning Advisor XML schema to perform "what-if" analysis.

To export tuning session results from the Database Engine Tuning Advisor GUI for "what-if" analysis with the dta command-line utility

  1. Use the Database Engine Tuning Advisor GUI to tune a database. See How to: Tune a Database by Using Database Engine Tuning Advisor. If you want to evaluate an existing tuning session, double-click it in the Session Monitor.

  2. On the File menu, click Export Session Results and save it as an XML file.

  3. Open the XML file created in Step 2 in your favorite XML editor, text editor, or in Microsoft SQL Server Management Studio. Scroll down to the Configuration element. Copy and paste the Configuration element section into an XML input file template after the TuningOptions element. Save this XML input file.

  4. In the new XML input file that you created in Step 3, specify any tuning options you want in the TuningOptions element, edit the Configuration element section (add or delete the physical design structures as appropriate for your analysis), save the file, and validate it against the Database Engine Tuning Advisor XML schema. For information about editing this XML file, see XML Input File Reference (Database Engine Tuning Advisor).

  5. Use the XML file that you created in Step 4 as input to the dta command line utility. For information about using XML input files with this tool, see How to: Tune a Database by Using the dta Utility.

Using the User-specified Configuration Feature with the dta Command Line Utility

If you are an experienced XML developer, you can create a Database Engine Tuning Advisor XML input file in which you can specify a workload and a hypothetical configuration of physical database design structures, such as indexes, indexed views, or partitioning. Then you can use the dta command-line utility to analyze the effects this hypothetical configuration has on query performance for your database. The following procedure explains this process step by step:

To use the user-specified configuration feature with the dta command line utility

  1. Create a tuning workload. For information about performing this task, see How to: Create Workloads.

  2. Copy and paste the XML Input File Sample with User-specified Configuration (DTA) into your XML editor or a text editor. Use this sample to create an XML input file for your tuning session. For information about performing this task, see How to: Create XML Input Files.

  3. Edit the TuningOptions and the Configuration elements in the sample XML input file. In the TuningOptions element, specify what physical design structures you want Database Engine Tuning Advisor to consider during the tuning session. In the Configuration element, specify the physical design structures that match the hypothetical configuration of physical database design structures that you want Database Engine Tuning Advisor to analyze. For information about what attributes and child elements you can use with the TuningOptions and the Configuration parent elements, see XML Input File Reference (Database Engine Tuning Advisor).

  4. Save the input file with an .xml extension.

  5. Validate the XML input file you saved in Step 4 against the Database Engine Tuning Advisor XML schema. This schema is installed at the following location when you install Microsoft SQL Server:

    C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\dta\dtaschema.xsd

    The Database Engine Tuning Advisor XML schema is also available online at https://schemas.microsoft.com/sqlserver/2004/07/dta.

  6. After creating a workload and an XML input file, you are ready to submit the input file to the dta command-line utility for analysis. Make sure that you specify an XML output file name for the -ox utility argument. This creates an XML output file with a recommended configuration specified in the Configuration element. If you want to run Database Engine Tuning Advisor again to check another hypothetical configuration that is based on the output, you can copy and paste the Configuration element contents from the output file into a new or your original XML input file. For information about using an XML input file with the dta utility, see the procedure "To tune a database using an XML input file" in How to: Tune a Database by Using the dta Utility.

    After tuning is finished, either use the Database Engine Tuning Advisor GUI to view the tuning reports, or open the XML output file to view the TuningSummary and the Configuration elements to view the Database Engine Tuning Advisor recommendations. For information about viewing the results of your tuning session, see How to: View Tuning Output. Also note that the XML output file may contain Database Engine Tuning Advisor analysis reports.

  7. Repeat steps 6 and 7 until you create the hypothetical configuration that produces the query performance improvement that you need. Then you can implement the new configuration. See How to: Implement Tuning Recommendations.

Security

For important information about the permissions required to use Database Engine Tuning Advisor, see Initializing Database Engine Tuning Advisor.