Understanding SQL Server Query Optimization – Part 4
Editor’s Note: In
four part series, I gave an overview of the tools and techniques of query
optimization in SQL Server. In the
talked about the process of displaying and interpreting query execution plans.
I also showed how to generate query execution plans using SQL Server Management
Studio, SHOWPLAN_TEXT and SHOWPLAN_ALL. In the
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
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.
Engine Tuning Advisor?
Tuning Advisor is a tool that uses a SQL Profiler trace file to make
recommendations about optimizing a database.
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.
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
procedure for using the Database Engine Tuning Advisor to fine-tune a database
the Database Engine Tuning Advisor or dta (command-line tuning utility).
the tuning output.
desired, run an exploratory analysis.
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
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:
SQL Server Profiler.
a new trace using the tuning template.
the trace to save to a file or database table.
This gives you a
workload file or table you can import into the Tuning Advisor to fine tune your
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:
Database Engine Tuning Advisor.
the database(s) and (optionally) table(s) to tune.
the workload file or table.
- Run the analysis.
You use the Tuning
Options tab to set tuning options. These include:
data structures (indexes, indexed views, etc.) to use.
physical data structures to keep after tuning.
- The Tuning Advisor
reports its tuning recommendations when finished.
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:
You can have the
command report tuning recommendations or apply the recommendations
automatically without prompting you.
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:
the Recommendations tab and remove any recommendations you don’t want
Actions, Evaluate Recommendations to launch a new tuning session.
the session and set any tuning options.
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:
the session results you want to apply.
Actions, Apply Recommendations.
either ‘Apply now’ or ‘Schedule for later’
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.
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.
information about views and functions that return index information, see
the following SQL Server Books Online article “
Dynamic Management Views and Functions
information about Database Tuning Advisor, see
Start and Use
the Database Engine Tuning Advisor
Engine Tuning Advisor