Tutorial: Database Engine Tuning Advisor
Welcome to the Database Engine Tuning Advisor tutorial. The Database Engine Tuning Advisor enables you to tune databases for improved query processing. Database Engine Tuning Advisor examines how queries are processed in the databases you specify and then it recommends how you can improve query processing performance by modifying physical design structures such as indexes, indexed views, and partitioning.
It replaces the Index Tuning Wizard from Microsoft SQL Server 2000, and offers many new features. For example, Database Engine Tuning Advisor provides two user interfaces: a graphical user interface (GUI) and the dta command prompt utility. The GUI makes it easy to quickly view the results of tuning sessions, and the dta utility makes it easy to incorporate Database Engine Tuning Advisor functionality into scripts for automated tuning. In addition, Database Engine Tuning Advisor can take XML input, which offers more control over the tuning process. For more information about Database Engine Tuning Advisor new features, see Database Engine Tuning Advisor Features.
What You Will Learn
This tutorial will teach you how to navigate the Database Engine Tuning Advisor GUI, and how to perform some basic tasks with both the GUI and the dta utility. It contains the following lessons:
Lesson 1: Basic Navigation in Database Engine Tuning Advisor
In this lesson, you will familiarize yourself with the new Database Engine Tuning Advisor GUI and learn how to set display options and layout.Lesson 2: Using Database Engine Tuning Advisor
In this lesson, you will learn how to perform basic tuning tasks with the Database Engine Tuning Advisor GUI.Lesson 3: Using the dta Command Prompt Utility
In this lesson, you learn how to start the dta command prompt utility and how to run some simple tuning commands.
Requirements
This tutorial is intended for users who are not familiar with the Database Engine Tuning Advisor GUI or the dta command prompt utility, but who are experienced database administrators who are familiar with database concepts and physical design structures such as indexes and indexed views.
AdventureWorks Sample Database
This tutorial uses the AdventureWorks2008R2 database, which is a new and larger sample database that can demonstrate the more complex features of SQL Server. AdventureWorksDW is a related database that supports Analysis Services. To enhance security, the sample databases are not installed by default. To install the sample databases, run Setup from Add or Remove Programs in Control Panel.
After You Finish This Tutorial
After you finish the lessons in this tutorial, refer to the following topics for more information about Database Engine Tuning Advisor:
Database Engine Tuning Advisor Overview for reference information, including a reference section describing the Database Engine Tuning Advisor XML schema
Tuning the Physical Database Design for conceptual information about using the tool and troubleshooting information
Database Engine Tuning Advisor How-to Topics for more information about how to perform tasks with this tool
Note
When reviewing tutorials it is recommended you add Next and Previous buttons to the document viewer toolbar. For more information, see Adding Next and Previous Buttons to Help.