The Database Tuning Advisor (DTA) is a tool provided
with SQL Server to help Developers and System Administrators improve
query performance by optimizing physical design structures such as
indexes, indexed views, and table partitioning. DTA can be launched from
the same folder location as SQL Server Profiler. Alternately, DTA can
be started from within Management Studio, where it is located on the
Tools menu.
In most cases, DTA speeds
up the query it’s tuning but adding indexes can have an adverse effect
on inserts and deletes. Be sure to review and test the suggestions it
offers.
|
DTA is a useful tool in
reviewing workload and making recommendations that can improve
performance. However, DTA is no silver bullet. Always review
recommendation and consider the impact of maintaining indexes once
they’ve been created. For example, with one test workload, DTA could
make a 90% performance improvement with 10 new indexes; however an 85%
improvement could be gained with a single index—in this case we might
elect to ignore the nine additional indexes.
|
There are two ways to use
DTA: either as a workload file or as a tool to tune a specific query.
Workload files can either be a SQL Profiler trace or a.sql file
containing a workload (queries). To tune a specific query, load the
query in SQL Server Management Studio, right-click the query, then
select Analyze Query in Database Tuning Advisor from the menu (see Figure 1).
Capturing a Workload
One
of the most useful features of DTA is workload analysis. This gives
developers and administrators the ability to capture a trace from a
production system and analyze it to identify changes that could improve
the performance of the workload. The effort required to capture and use
DTA to analyze the workload trace is relatively little compared with the
potential performance gain. Additionally, the right-click analyze query
- DTA function provides developers with opportunity to evaluate a query
prior to changing the query in production – actively looking for
missing statistics or indexes.
To achieve the best
results, a workload trace should contain activity from the live
application where optimization is to be performed. Using the Tuning
trace template within SQL Profiler provides sufficient detail to enable
analysis with DTA as shown in Figure 2.
If you prefer to define a trace manually, include the following events:
RPC:Completed
SP:StmtCompleted
SQL:BatchCompleted
Trace files can be provided to
DTA as .trc files (the native trace output files from SQL Profiler) or
from a table within a database (if you’ve traced from SQL Profiler
directly to a table). The performance burden on the live system is
minimized by tracing to a flat file.
Performing Analysis with DTA
Once
the workload has been captured to a trace file or a specific query has
been identified for review, launch DTA and connect to the SQL Server
instance where the database to be analyzed resides.
|
During the tuning process,
DTA can make multiple calls to the Query Optimizer within the database
engine to determine benefits and impacts of physical design structure
changes to query plans. If you run DTA against a production server,
performance could be affected during analysis. It’s recommended that you
run DTA on a test server from a restored database backup to avoid any
performance degradation.
|
Use the General
tab within DTA to locate the workload file (i.e., the file containing
tsql workload or the trc file created using SQL Profiler). If there are
multiple trace files (created using the file rollover option within SQL
Profiler), select the first file, and then subsequent files will be read
in sequence automatically. Next, select the checkbox alongside the
target database for tuning. If you’re tuning a specific query or .tsql
workload file, you can save time during analysis by narrowing the
selection of tuning tables within the database. If the workload file is a
SQL trace, this may not be possible since there’s no easy way to
determine the tables involved in the contents of the trace workload. In
this case, run DTA against the entire database (see Figure 3).
Select the Tuning Options tab to review a number of alternatives. Click the Limit tuning time
check box and indicate a stop time of one hour in the future to be
certain you receive results within a reasonable period of time (see Figure 4).
Additionally, review the three categories that follow in the window to
indicate your objectives and ensure any restrictions within your
operating environment can be satisfied by the recommendations generated
from DTA. To continue, click Start Analysis on the menu bar.
During
analysis, the Progress tab displays each step undertaken by DTA. If
there are any errors reading the workload files or missing events in the
case of SQL traces, these are displayed with hyperlinks for further
information on remediating the problem.
Following completion
of your analysis, the Recommendations tab is displayed. Note at the top
of the recommendations screen, DTA makes an estimation of the
improvements that could be achieved by implementing the recommendations.
In Figure 5,
there is a combination of indexes and statistics recommended by DTA:
the default DTA object naming convention shows new indexes with prefix
_dta_index_<objectidentifier> and statistics within prefix
_dta_stat_<objectidentifier>.
Finally, select the Reports
tab for a summary of the analysis including details of the
recommendations (such as information about proposed indexes including
sizes, definition, and number of rows). Additionally, there are reports
available to summarize events within the workload file such as tables
and column access reports.
Implementing DTA Recommendations
Once DTA has run the
analysis, its recommendations can either be applied immediately or
scripted to be tested and applied at a later date. Script
recommendations by selecting Save recommendations from the Actions menu.
