Using Database Engine Tuning Advisor

Database Engine Tuning Advisor is a performance tool that allows you to examine workload files, such as trace and .sql files, and advise changes in physical database structures, such as indexes, index views, and partitioning, to achieve your best performance. Database Engine Tuning Advisor can also recommend what statistics should be collected to support physical structures.

Analyzing Database Engine Tuning Advisor

Database Engine Tuning Advisor has several tuning capabilities that are always based on the workload files used as input. It is critical that the workload files include a load representative of the database activity. Database Engine Tuning Advisor can:

• Use the Query Optimizer to propose indexes and indexed views.

• Recommend partitions.

• Analyze the impact of recommended changes.

• Provide information about query distribution and index usage.

You can set all of these options for each session. You will learn about options and sessions in following sections of this chapter.

Working with Database Engine Tuning Advisor

To use Database Engine Tuning Advisor, you should first create a workload file. This workload file can be a trace file created with SQL Server Profiler, T-SQL code that you want to analyze, or an .xml file. Once you have created the workload files, proceed to the following steps.

Starting Database Engine Tuning Advisor

1. Start Database Engine Tuning Advisor from the Start menu by choosing All Programs Microsoft SQL Server 2005 Performance Tools Database Engine Tuning Advisor.

2. Connect to the SQL Server instance.

3. A new Tuning Advisor session appears. You can change the session name in the Session Name textbox, as shown below.

   

4. In the Workload frame, choose the File option. Click the button to the right of the textbox to browse for a file and navigate to \Ch03\DTATrace.trc in the sample files.

5. In the Database For Workload Analysis drop-down list, select AdventureWorks.

6. In the Databases And Tables To Tune frame, select AdventureWorks. Note that you can click the cell in the Selected Tables column to specify individual tables.

7. Select the Tuning Options tab, which allows you to configure session behavior and recommendation constraints. You will learn more about these options later in the Managing Tuning Options section of this chapter.

8. Click the Start Analysis toolbar button.

When the Database Engine Tuning Advisor ends (this will take a few seconds), you will observe two new tabs: Recommendations and Reports . The Recommendations tab presents Tuning Advisor recommendations based on the workload file and tuning options. To view only the recommended changes, you can uncheck the Show Existing Objects checkbox, as shown in Figure 3-19.

Figure 3-19: Database Tuning Advisor Recommendations.

In the Recommendation column, you see the actions that can be created, altered , or dropped. In the Definition column, you see the T-SQL code used to implement the recommendation. You can also save all of the recommendations by selecting Save Recommendations from the Actions menu. If you select the Reports tab, you can choose a report from the Select Report drop-down list to view detailed information about the analysis, including index usage reports (both before and after the recommendation is implemented) and a statement-index relations report.

Evaluating the Recommendations

One of the most interesting Database Engine Tuning Advisor features is the ability to evaluate the impact of the recommendations.

Evaluating the Impact of Database Engine Tuning Advisor Recommendations

1. In the Database Engine Tuning Advisor, from the Actions menu, choose Evaluate Recommendations. A new session is created to evaluate the impact.

2. Click the Start Analysis button.

When the analysis ends, you will receive information concerning how the recommendations would affect the database.

Managing Tuning Options

There are many options available to use for configuration purposes in a Database Engine Tuning Advisor session. You can set the following options on the Tuning Options tab.

• Physical Design Structures (PDS) To Use In Database   Allows you to choose which database objects the Database Engine Tuning Advisor can recommend using to achieve best performance.

• Partitioning Strategy To Employ   Allows you to choose whether and what kind of partitions Database Engine Tuning Advisor will advise using to optimize the workload.

• Physical Design Structures (PDS) To Keep In Database   Allows you to choose whether you want to maintain all database objects or will consider recommendations to drop certain objects.

You can configure advanced options by clicking the Advanced Options button. Advanced options include:

• Define Max Space For Recommendations   Allows you to specify the maximum space used for recommendations.

• Max Columns Per Index   Allows you to specify the maximum number of columns per index.

• Online Index Recommendations   Allows you to choose whether index operations will be online or offline.

Managing Database Engine Tuning Advisor Sessions

Database Engine Tuning Advisor uses sessions to manage analysis operations. Earlier in this section, you created two Tuning Advisor sessions: one for the initial analysis and another for the evaluation session. You can create as many sessions as you need, close them, and import and export sessions from the Database Engine Tuning Advisor.

Exporting a Session Definition

1. In Database Engine Tuning Advisor from the File menu, choose Export Session Definition.

2. Navigate to \Ch03. Save the definition as SessionDefinition.xml .

Once the definition has been exported, you can import it into another SQL Server instance. These export and import operations allow you to efficiently deploy Database Engine Tuning Advisor sessions in several SQL Server instances.

Session information is stored in the msdb database to allow you to reuse sessions. All Database Engine Tuning Advisor- related tables start with the prefix DTA_ so they can be detected easily.