Database Reference
In-Depth Information
Chapter 10
Database Engine Tuning Advisor
SQL Server's performance frequently depends upon having the proper indexes on the database tables. However,
as the workload and data change over time, the existing indexes may not be entirely appropriate, and new indexes
may be required. The task of deciding upon the correct indexes is complicated by the fact that an index change that
benefits one set of queries may be detrimental to another set of queries.
To help you through this process, SQL Server provides a tool called the Database Engine Tuning Advisor.
This tool can help identify an optimal set of indexes and statistics for a given workload without requiring an expert
understanding of the database schema, workload, or SQL Server internals. It can also recommend tuning options for a
small set of problem queries. In addition to the tool's benefits, I cover its limitations in this chapter, because it is a tool
that can cause more harm than good if used without deliberate intent.
In this chapter, I cover the following topics:
•
How the Database Engine Tuning Advisor works
•
How to use the Database Engine Tuning Advisor on a set of problematic queries for index
recommendations, including how to define traces
•
The limitations of the Database Engine Tuning Advisor
Database Engine Tuning Advisor Mechanisms
You can run the Database Engine Tuning Advisor directly by selecting Microsoft SQL Server 2012
➤
Performance
Tools
➤
Database Engine Tuning Advisor. You can also run it from the command prompt (
dta.exe
), from SQL
Profiler (Tools
➤
Database Engine Tuning Advisor), from a query in Management Studio (highlight the required
query, and select Query
➤
Analyze Query in the Database Engine Tuning Advisor), or from Management Studio
(select Tools
➤
Database Engine Tuning Advisor). Once the tool is open and you're connected to a server, you
should see a window like the one in Figure
10-1
. I'll run through the options to define and run an analysis in this
section and then follow up in the next session with some detailed examples.
