Learn more about SQL Server tools

 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Database Engine Tuning Advisor



By:
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.

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.












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    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools