By: Greg Robidoux
Overview
SQL Server also includes another performance tool called the Database Engine Tuning Advisor or DTA. This tool allows you to have SQL Server analyze one statement or a batch of statements that you captured by running a Profiler or server side trace. The tool will then go through each statement to determine where improvements can be made and then presents you with options for improvement.
Explanation
The Database Engine Tuning Advisor is basically a tool that helps you figure out if additional indexes are helpful as well as partitioning. Here is a summary of the options:
- Adding indexes (clustered, non-clustered, and indexed views)
- Adding partitioning
- Adding statistics including multi-column statistics which are not created automatically even when you have the AUTO_CREATE_STATISTICS database option set to ON
In addition to identifying opportunities for improvement, DTA will also create a T-SQL script that you can run to actually implement its recommendations.
Here is an example of a query and how we can use DTA to analyze the query and make recommendations. From within a query window right click and select the DTA option as shown.
Here are the available options for tuning.
After you select the specific options click on Start Analysis and this will run the DTA tool to identity any potential improvements.
Here we can see that DTA recommends adding a new index for table DimProduct.
The Database Engine Tuning Advisor can also be launched from within SSMS by clicking on Tools > Database Engine Tuning Advisor.
Additional Information
Here are some additional items related to the Database Engine Tuning Advisor.
- SQL Server Database Engine Tuning Advisor for Performance Tuning
- Error running the SQL Server Database Tuning Advisor for non SA users
