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
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,
Here we can see from the execution plan the ‘Estimated Subtree Cost’ is 0.713995. The goal is to reduce this cost.
Execute the selected query from the editor, then right click on the query and select the ‘Analyze Query in Database Engine Tuning Advisor’ option.
In this step, click the query radio button and checkbox the database for which you want to tune.
Once we click the dropdown button next to the database. Here we have an option to choose the particular tables for tuning purpose.
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.
On the previous page click on the advanced options and define the max space for recommendation in (MB) and max columns per index.
Step 5: In this step, choose start analysis option.
Now the tuning analysis has begun for the given query.
Once the tuning has completed successfully, the tuning advisor will give recommendations on the estimated improvements. Here the estimated improvements was 96%.
The recommendation given by the advisor is to create non-clustered index script for the further improvements.
We can apply the recommended improvements as script or can choose from the Actions tab-> ‘Apply recommendations’ option.
Once the recommendation is finished the below success message will be displayed.
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.