Database Engine Tuning Advisor Features

Database Engine Tuning Advisor provides new features that enable both novice and experienced database administrators to tune databases for better query performance. The following sections list and describe the Database Engine Tuning Advisor new features:

Improved Workload Parsing

• Handles batches that reference transient tables such as temp tables.
• Does not terminate tuning because it cannot parse an event. Instead, it logs the event into the tuning log and then proceeds with tuning other events.
• Parses and tunes queries referencing user-defined functions.
• Handles all USE statements in a trace, which is essential when tuning multiple databases.
• Uses the LoginName column in the workload (when available in trace workloads) to correctly tune the event in the context of the user who executes it.
• Tunes statements in triggers.

Enhanced Scalability

• Uses workload compression, which allows Database Engine Tuning Advisor to reduce the amount of time spent tuning while producing high quality tuning recommendation results.
• Uses an algorithm to avoid redundant statistics creation, which reduces the number of I/Os incurred during tuning.

Integrated Tuning

Database Engine Tuning Advisor can consider the trade offs in performance that are offered by various different types of physical design structures (indexes, indexed views, partitioning). Different structures can overlap in their ability to reduce the execution cost for any given query, so having the capability to consider an integrated configuration of multiple design structures enables Database Engine Tuning Advisor to provide higher quality recommendations. For example, considering only clustered indexes on a set of tables and then considering horizontal range partitioning on the same set of tables does not tell you how these two physical design structures work together. To understand how they work together, your tuning tool must perform integrated tuning.

Multiple Database Tuning

Applications often access multiple databases to complete their work, so workloads frequently refer to objects from multiple databases. Database Engine Tuning Advisor can simultaneously tune multiple databases, unlike the Index Tuning Wizard in Microsoft SQL Server 2000. Users can specify a set of databases to tune, and Database Engine Tuning Advisor makes recommendations for all selected databases. For more information about this feature, see Tuning Multiple Databases.

Offload of Tuning Overhead to Test Server

Tuning a large workload can create significant overhead on the server that is being tuned. This occurs because Database Engine Tuning Advisor often needs to make several calls to the query optimizer during the tuning process. Using a test server in addition to your production server eliminates this problem.

In this scenario, you tune a 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 implement the recommendation on your production server during a maintenance window.

The traditional way to use a test server is to copy all of the data from your production server to your test server, tune the test server, and then adjust the resulting recommendation for hardware differences between the two computers. Database Engine Tuning Advisor can exploit the use of a test server without requiring that you copy the data over to the test computer or without requiring that the two sets of computer hardware be identical, which saves you time and resources. Instead, Database Engine Tuning Advisor imports the metadata, statistics, and hardware parameters from the production server to the test server. Then Database Engine Tuning Advisor performs the bulk of tuning operations on the test server, and you can implement the results on your production server during normal maintenance. For more information about this feature, see Reducing the Production Server Tuning Load.

Command-Prompt Utility and Graphical User Interface Tool

Database Engine Tuning Advisor provides the following two user interfaces:

• A dta command-prompt utility to make it easy to incorporate Database Engine Tuning Advisor functionality with scripting.
• A Database Engine Tuning Advisor graphical user interface (GUI) tool, which makes it easy to view tuning sessions and results.

Drop-Only Tuning

Physical design structures may accumulate over time in a database, and database administrators need a way to drop those structures that are no longer useful. The drop-only tuning option is useful for identifying existing physical design structures that are either not used at all or have very little benefit for a given workload. This tuning option can be specified with the –fx argument when you are using the dta command-prompt utility. When you are using the GUI, you can specify this option by selecting Evaluate utilization of existing PDS only on the Tuning Options tab.

With this option, Database Engine Tuning Advisor never proposes any new structures, so you cannot use this option with others that add indexes, indexed views, or partitions. You also cannot use the drop-only tuning option with the option to keep all existing structures. For more information about the tuning options you can use with Database Engine Tuning Advisor, see Available Tuning Options.

XML Support

All of the tuning operations that you can perform with the Database Engine Tuning Advisor GUI and the dta command-prompt utility can be defined in a Database Engine Tuning Advisor XML input file. The XML input file uses the published Database Engine Tuning Advisor XML schema, which can be found at the following location in your Microsoft SQL Server 2005 installation directory:

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

You can also download the schema from Database Engine Tuning Advisor Schema on the Microsoft website.

Using an XML input file enables you to use your favorite XML tools when tuning databases, and it provides experienced database administrators with more flexibility. For example, using an XML input file, you can specify a configuration of existing and hypothetical physical design structures (indexes, indexed views, partitions) and then use the dta command-prompt utility to tune a database as if this configuration was already implemented. This enables "what-if" analysis without incurring the overhead of implementing the new configuration before tuning. For more information about Database Engine Tuning Advisor XML support, see XML Input File Reference (DTA), Using an XML Input File for Tuning, and Exploratory Analysis Using Database Engine Tuning Advisor.

User-specified Configuration and "What-if" Analysis Support

Database Engine Tuning Advisor enables users to provide a hypothetical configuration of physical design structures (indexes, indexed views, and partitioning strategies) as tuning input. You can then tune a database or databases as if the configuration were already implemented. This enables you to ask "what-if" about a particular set of physical design structures before you incur the overhead of implementing them. This feature is fully supported by the Database Engine Tuning Advisor XML schema. You can specify the configuration you want to evaluate in the XML input file and then use the dta command-prompt utility to start the tuning session. For more information about this feature, see Exploratory Analysis Using Database Engine Tuning Advisor.

Analysis Reports

After a tuning session completes, Database Engine Tuning Advisor generates several analysis reports in text or in XML. These reports provide information such as the cost of queries occurring in the workload, the frequency of events that occur in the workload, or the relationships between queries and the indexes they reference. You can use the Database Engine Tuning Advisor GUI to view the reports, or you can use your favorite XML editor to open the reports that are generated in XML. For more information about the analysis reports that Database Engine Tuning Advisor generates, see Choosing a Database Engine Tuning Advisor Report.

Tuning Sessions

Before analysis can begin, Database Engine Tuning Advisor prompts you to create a unique name or number to identity the tuning session. After analyzing a workload or when analysis is stopped, Database Engine Tuning Advisor saves the tuning session information and associates it with the identifier you supplied before tuning began. You can use the session identifier to reload an existing session into the Database Engine Tuning Advisor GUI where you can review the tuning results and reports. Tuning sessions make it easy to compare tuning results over a period of time. For more information about using tuning sessions, see Using Session Monitor to Evaluate Tuning Recommendations.

Features Only Available with the dta Utility

Tuning options listed in the following sections are not currently available when you use the Database Engine Tuning Advisor GUI.

From a Command Prompt or When Using an XML Input File

When you run the dta utility from a command prompt or if you use an XML input file with the utility by using the -ix command-prompt argument, you can specify:

• A tuning log by name (-e command-prompt argument, or TableName element).
• The number of events to tune (-n command-prompt argument, or NumberOfEvents element).
• The maximum number of key columns in indexes to be proposed by Database Engine Tuning Advisor (-c command-prompt argument, or MaxKeyColumnsInIndex element).
• The minimum improvement for Database Engine Tuning Advisor to propose for a recommended configuration (-m command-prompt argument, or MinPercentageImprovement element).

When Using an XML Input File

In addition to the features above, when you use an XML input file with the dta utility, you can also:

• Use the test server/production server scenario, which includes creating a shell database on a test server (TestServer element).
• Specify a workload inline in the XML input file (EventString element).
• Ignore constants in a workload (IgnoreConstantsInWorkload element).

From a Command Prompt

In addition to the features above, when you run the dta utility from a command prompt, you can also:

• Specify the quiet mode where the utility runs without displaying any feedback messages (-q command-prompt argument).
• Tune and automatically apply the tuning recommendations (-a command-prompt argument).

See Also

Other Resources

dta Utility
Using an XML Input File for Tuning
Reducing the Production Server Tuning Load

Help and Information

Getting SQL Server 2005 Assistance

Change History