Understanding SQL Server Query Optimization – Part 4

Editor’s Note: In
of this
four part series, I gave an overview of the tools and techniques of query
optimization in SQL Server. In the
part, I
talked about the process of displaying and interpreting query execution plans.
I also showed how to generate query execution plans using SQL Server Management
part, I
talked about the index-related dynamic management functions (DMFs) and views
(DMVs). In this fourth and final part, I will talk about how to create a
workload and use the workload and Database Engine Tuning Advisor to tune a
database indexes.

In my previous
article, I talked about the index-related dynamic management functions (DMFs)
and views (DMVs) that are helpful to find
potential missing indexes for your database queries. However, the
missing indexes recommendations provided by index-related dynamic management
functions (DMFs) and views (DMVs) is recommended as a general guideline only.
You shouldn’t use the information for fine-tuning a database. This is because
the identified missing indexes are the ones that the SQL Server optimizer
identified during query compilation, and are targeting a specific query. Therefore,
to create an optimal set of indexes, indexed views, or table partitions, Microsoft
recommends submitting your workload for query optimisation to the Database
Tuning Advisor for further evaluation.

What is
Engine Tuning Advisor?

Database Engine
Tuning Advisor is a tool that uses a SQL Profiler trace file to make
recommendations about optimizing a database.

Query performance
optimization can be difficult without a full understanding of the database
structure and the queries running against the database. The DTA can make this
task easier by analysing the current query cache or by analysing a workload of
Transact-SQL statements to make recommendations about database physical design
to fine tune your database.

Database tuning

You use the
Database Engine Tuning Advisor to analyse database activity based on a workload
file or table. The Database Engine Tuning Advisor returns suggested index
changes. The better (more representative) the workload, the better the index
tuning suggestions.

General procedure

The general
procedure for using the Database Engine Tuning Advisor to fine-tune a database

  • Generate
    a workload.
  • Run
    the Database Engine Tuning Advisor or dta (command-line tuning utility).
  • View
    the tuning output.
  • If
    desired, run an exploratory analysis.
  • Implement
    the recommendations.

Because both your
database environment and business requirements can change over time, you should
periodically run through the tuning process. General procedures are covered in
this article. For more detailed information, refer to the “

Start and Use the
Database Engine Tuning Advisor


Tutorial: Database
Engine Tuning Advisor

articles in SQL
Server Books Online.


Your first step is
generating a workload. You can use a trace file, trace table, or embed the
workload in an XML input file. The advantage of using an XML input file is that
you can assign weights to events. Otherwise, they are all treated the same.
Detailed information about using an XML input fileis beyond the scope of this article.
To create a workload:

  • Launch
    SQL Server Profiler.
  • Create
    a new trace using the tuning template.
  • Configure
    the trace to save to a file or database table.
  • Capture
    system activity.
  • Save
    the workload.

This gives you a
workload file or table you can import into the Tuning Advisor to fine tune your

Database tuning
with the Database Engine Tuning Advisor

You use the
Database Engine Tuning Advisor or the dta command to tune the database. To use
Database Engine Tuning Advisor:

  • Launch
    Database Engine Tuning Advisor.
  • Select
    the database(s) and (optionally) table(s) to tune.
  • Open
    the workload file or table.
  • Set
    tuning options.
  • Run the analysis.

You use the Tuning
Options tab to set tuning options. These include:

  • Physical
    data structures (indexes, indexed views, etc.) to use.
  • Partition
  • Existing
    physical data structures to keep after tuning.
  • The Tuning Advisor
    reports its tuning recommendations when finished.

Database tuning
with the dta command

The dta command is
an operating system command-line command. It performs the same function as the
Database Engine Tuning Advisor, analysing a workload and returning tuning
recommendations. You can specify the target database(s) and table(s). As workload
input, you can specify a:

  • Workload
  • Workload
  • XML
    input file

You can have the
command report tuning recommendations or apply the recommendations
automatically without prompting you.

Viewing tuning

The Database Engine
Tuning Advisor reports recommendations automatically. You can also view the
current recommendations at any time after running the analysis by activating
the Recommendations tab. For a previous session, you select the session in the
Session Monitor pane and activate Recommendations. You activate the Reports table
to view a tuning summary report.

You can save the
recommendations for later review. Before starting the analysis, in the General
page of the Tuning Advisor, check “Save tuning log.” You can export the session
results to an XML file after running the analysis. Choose File, Export Session
Results, then select the destination folder and enter the XML file name. The
Tuning Advisor saves the results as a well-formed XML document.


‘What if’ analysis, trying different options and parameters and comparing the
results. In essence, it is running multiple analysis sessions based on the same
workload. To evaluate any tuning session:

  • Activate
    the Recommendations tab and remove any recommendations you don’t want
  • Choose
    Actions, Evaluate Recommendations to launch a new tuning session.
  • Name
    the session and set any tuning options.
  • Click
    Start Analysis.

You can run another
analysis using the same data, but with different analysis parameters.


The usual goal of
running the tuning advisor is generating a set of tuning recommendations you
can apply to your database(s). The dta utility can apply recommendations
automatically. When using the Database Engine Tuning Advisor, you must apply
the recommendations manually. To apply the recommendations:

  • Select
    the session results you want to apply.
  • Choose
    Actions, Apply Recommendations.
  • Select
    either ‘Apply now’ or ‘Schedule for later’
  • Click

If you choose
“Schedule for later,” you must set the schedule (the date and time) for
applying the recommendations. It might take multiple attempts before you have
an appropriate set of recommendations. Don’t apply the changes until you have
the tuned the database to your specific requirements.

What Next?


The final step in
the optimization process, whether optimizing the hardware platform, database
server, database, or application, is testing and verifying that your changes
have the desired result. You run the same tests with the same parameters after
optimization as your ran before.

Further Reading:


No comments yet... Be the first to leave a reply!