Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


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.

Last Update: 12/30/2011

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools