SQL Server Database Engine Tuning Advisor

The Database Tuning Advisor (DTA) is a tool that comes with the SQL Server and is used to analyze a workload and give recommendations that will enhance query performance.  The DTA makes recommendation to the following:

  • Adding indexes (clustered, nonclustered, and indexed views)
  • Adding partitioning
  • Adding statistics

Note, before we can use the DTA, we must have a workload file to work against.  This workload is a collection of data gathered by the SQL Profiler.

You can use direct queries, trace files, and trace tables generated from SQL Server Profiler as workload input when tuning databases.

When you start analysis with Database Engine Tuning Advisor, it analyzes the provided workload and recommends to add, remove, or modify physical design structures in your databases like creating indexes and partitioning. It also recommends if additional statistics objects need to be created to support physical design

Step 1:

First, choose the database for which you want to tune. In this blog, I have used Adventureworks2014 as my database for tuning.

I have used the below query for tuning from the Adventureworks2014,

Image for post

Here we can see from the execution plan the ‘Estimated Subtree Cost’ is 0.713995. The goal is to reduce this cost.

Image for post

Step 2:

Execute the selected query from the editor, then right click on the query and select the ‘Analyze Query in Database Engine Tuning Advisor’ option.

Image for post

Step 3:

In this step, click the query radio button and checkbox the database for which you want to tune.

Image for post

Once we click the dropdown button next to the database. Here we have an option to choose the particular tables for tuning purpose.

Image for post

Step 4:

On top of the tool, we have the tuning tab option click on that we can set the tuning stop date & time, and we can use the PDS option for the database according to your requirements. Moreover partitioning strategy option can also be implemented accordingly.

Image for post

On the previous page click on the advanced options and define the max space for recommendation in (MB) and max columns per index.

Image for post

Step 5: In this step, choose start analysis option.

Image for post

Now the tuning analysis has begun for the given query.

Image for post

Step 6:

Once the tuning has completed successfully, the tuning advisor will give recommendations on the estimated improvements. Here the estimated improvements was 96%.

Image for post

The recommendation given by the advisor is to create non-clustered index script for the further improvements.

Image for post

Step 7:

We can apply the recommended improvements as script or can choose from the Actions tab-> ‘Apply recommendations’ option.

Image for post

Once the recommendation is finished the below success message will be displayed.

Step 8:

Image for post

Finally when we run the query plan again, we can see that the cost has been reduce significantly from 0.713995 (as shown in step 1) to 0.0261428 and the clustered index scan has been changed to non-clustered index seek.

Image for post