Database Engine Tuning Advisor


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.


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.

launch database engine tuning advisor

Here are the available options for tuning.

dta settings and options

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.

Database Engine Tuning Advisor recommendations

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.

Comments For This Article

get free sql tips
agree to terms