Database Tuning Advisor

Database Engine Tuning Advisor (DTA) is a brand new tool that replaced the previously shipped Index Tuning Wizard (ITW). However, this is not just a name change. DTA is a full-blown application that has many powerful features never available in the old tool.

Let's try to start a simple tuning session to better understand DTA's capabilities. You can launch the tool from the Start menu. DTA is located under the Microsoft SQL Server 2005\Performance Tools folder.

Tips and Tricks

Using the Start menu is not the only way to launch DTA. The tool can be launched from within SQL Server Management Studio Query Editor window by following the Analyze Query in Database Engine Tuning Advisor menu selection. In this case DTA will be launched with pre-populated workload information, connection, and database context. If a portion of the text was selected in Query Editor window, it will become the default query to be analyzed instead of the entire script.

Tips and Tricks

Upon initial installation of SQL Server 2005, you need to run the tool first by connecting to Database Engine as a member of the sysadmin fixed server role. During the initial connection, DTA goes through an initialization process by creating special objects on the SQL Server Database Engine instance. After the first run, the application repository database is fully initialized, and from that point forward any user who is a member of the db_owner fixed database role can use DTA to tune tables on databases on his or her own.

Immediately after launching the tool you are presented with a connection dialog. After you establish a Database Engine connection you come to a new session creation screen, shown in Figure 7-19.

Figure 7-19. Database Tuning Advisornew session creation.

The first thing to note is the Session Monitor window in the left pane. Every session configuration, including tuning results, is stored inside the MSDB database of the SQL Server Database Engine Instance; therefore, it can be retrieved later at any point. In fact, along with performing tuning in real timethat is, while the user is watching the screenDTA supports disconnected tuning mode, where the graphical tool is fully closed but tuning continues behind the scene until finished. The user can always connect back to the server to monitor tuning progress. Thus, the purpose of Session Monitor is to present a list of all tuning sessions currently stored on the server. The default session name is fairly descriptive and comprises the current SQL Server Login name and timestamp when the session was first created.

The next thing to note is the database and table selection grid. DTA makes it possible to do narrow tuning to a specific set of databases and even tables within those databases. There is also a concept of a default database ("Database for workload analysis") to be used for workload analysis when the query itself does not specify the "use database" clause and does not contain fully-qualified object names.

Before you go any further, it is critical to supply either a workload file or a workload table to the tuning engine. DTA accepts three types of workload files: trace files that can be opened by SQL Profiler, T-SQL query scripts, and XML files. Using XML files as a workload input is discussed later in this chapter, during the description of the DTA internal architecture.

Tips and Tricks

When DTA consumes a trace table or file that contains the "LoginName" column as a workload, it impersonates the user specified by that column during tuning. If this user does not have sufficient permissions to execute and produce Showplans for the statements contained in the trace, DTA cannot tune to those statements. To resolve this you can either grant Showplan permission to each affected user or remove the "LoginName" column from those events that are not tuned.

DTA Tuning Options

Switch to the Tuning Options tab to examine the DTA tuning options shown in Figure 7-20.

Figure 7-20. Database Tuning Advisor session tuning options.

The first thing to note is a time limit on how long tuning should proceed. DTA runs for a predetermined amount of time and gives the best set of recommendations it's able to come up with under the constraints. This enables you to schedule tuning in the time window allocated for database maintenance without affecting other clients.

A list of different tuning options and their meanings is presented in the Table 7-1.

Table 7-1. Tuning Options

Option	Meaning
Indexes and indexed views	Include recommendations for adding clustered/nonclustered indexes and indexed views.
Indexed views	Include only recommendations for adding indexed views.
Indexes	Include only recommendations for adding clustered and nonclustered indexes.
Nonclustered indexes	Include only recommendations for nonclustered indexes.
Evaluate utilization of existing PDS only	Evaluate the effectiveness of the current indexes, but do not recommend additional indexes or indexed views.
No partitioning	Do not recommend partitioning.
Full partitioning	Include recommendations for partitioning.
Aligned partitioning	New recommended partitions will be aligned to make partitions easy to maintain.
Do not keep any existing PDS	Recommend dropping unnecessary existing indexes, views, and partitioning for a given workload.
Keep indexes only	Keep all existing indexes but recommend dropping unnecessary indexed views, and partitioning.
Keep all existing PDS	Keep all existing indexes, indexed views, and partitioning.
Keep clustered indexes only	Keep all existing clustered indexes but recommend dropping unnecessary indexed views, partitions, and nonclustered indexes.
Keep aligned partitioning	Keep partitioning structures that are currently aligned, but recommend dropping unnecessary indexed views, indexes, and non-aligned partitioning. Any additional partitioning recommended will align with the current partitioning scheme.

Advanced tuning options are shown in Figure 7-21.

Figure 7-21. DTA advanced tuning options.

Here you have an opportunity to limit the maximum space consumed by recommended physical data structures and the maximum number of columns per index, covering included columns as well.

This enables users to impose online recommendation constraints on the tuning engine.

Assuming you are happy with all selected tuning options, you can start analysis from the toolbar or by selecting the Actions, Start Analysis menu item.

The first phase of the tuning process as indicated by the progress report is called "Submitting Configuration Information." During this phase, all tuning options become part of the newly generated configuration XML that gets entered into the MSDB database through a special configuration stored procedure. After the tuning session is fully configured, the tuning engine gets invoked. While running, the tuning engine starts consuming a workload file or table data, at the same time performing incremental tuning analysis. Finally, reports and recommendations are generated and ready for examination.

At this point, the user can selectively apply recommendations right away, schedule applying recommendations later, or save the recommendations script for future use. All these options are available from the Actions menu. Before applying recommendations you can selectively choose which ones you would like to apply by using check boxes, as shown in Figure 7-22.

Figure 7-22. DTA recommendations.

You can even evaluate the impact of applying a partial set of recommendations via a feature called "Evaluative Tuning" described later in this chapter.

DTA Architecture

The DTA Architecture is described in Figure 7-23.

Figure 7-23. DTA architectural diagram.

There is a command line interface to DTA called DTA.exe. It supports most of the options exposed through the user interface.

Tips and Tricks

You can always get a full list of options supported by the tool by using the -? switch as follows:

C:\>DTA.exe -? 

When invoked with the u flag, DTA.exe launches a graphical interface to DTA called DTAShell.exe (1) and passes all supplied options to it as well. If a user intends to perform tuning via the console, DTA.exe makes direct calls to the DTAClient.dll (2) shared component. DTAShell.exe also uses shared components for tuning (3). The DTAClient.dll component contains the necessary infrastructure to create a proper XML configuration file and store it in the MSDB database. When the tuning session is created, it calls the DTAEngine90.exe component that actually performs the tuning. Because the DTAEngine90.exe is running as a separate process, it is possible to close the graphical tool and continue tuning in the background.

DTA interactions between DTAClient.dll and DTAEngine90.exe always happen via reading and writing to the MSDB database, as shown in Figure 7-24.

Figure 7-24. DTA component interactions via MSDB database.

Before first launching the DTAEngine90.exe process, the DTAClient.dll writes session information into the database (1). After it is running, DTAEngine90.exe consumes the information straight from MSDB (2). As tuning progresses it continues to update progress information in MSDB, eventually producing tuning recommendations (3). Tuning recommendations and reports are consumed by console or graphical tool from MSDB (4). This closes the interaction loop between DTA components.

Tips and Tricks

All information exchanged between different DTA components is in XML. 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\2003\03\dta\dtaschema.xsd

Evaluative Tuning

One very important action available after tuning is done is "Evaluate Recommendations." You can use this to pick and choose from a set of recommendations provided by the tool to evaluate the impact of applying a subset of those recommendations. When this menu item is selected, a new tuning session is created with essentially the same options as the old one, but a modified configuration XML section now contains the subset of selected recommendations.

Tips and Tricks

You don't have to stop with working only with a subset of given recommendations. You can actually learn the format of the XML configuration file and supply your own custom recommendations to the tool for evaluation. To see the configuration section, use the link provided at the bottom of the session screen (see Figure 7-25).

Figure 7-25. DTA configuration section of XML document.

Figure 7-25 shows the session configuration XML preview dialog.

Even though the graphical tool does not support direct editing of the configuration section of the XML, a configuration XML file can be provided as an input to the console program DTA.exe described earlier via the "-ix" option.