SQL Server Database Engine Tuning Advisor for Performance Tuning

The Database Engine Tuning Advisor (DTA) is a performance tuning tool that was first available with SQL Server 2005.  DTA replaced the Index Tuning Wizard in previous versions of SQL Server.  In a nutshell DTA analyzes a workload and makes recommendations that will enhance query performance.

There are several ways that you can launch DTA:

Use the Start menu and navigate to the Performance Tools folder in the SQL Server program group.

Select DTA from the Tools menu in SQL Server Management Studio (SSMS).

Select DTA from the Tools menu in SQL Server Profiler.

Select Analyze Query in Database Engine Tuning Advisor on the SSMS Query menu from a query window, allowing you to pass a T-SQL batch to DTA for analysis.

Type DTA from a Command Prompt; add -? to the command line to see the available options.

You typically create a workload by putting together a group of queries in a text file or by running SQL Server Profiler.  The key point to consider is that the workload you pass to DTA for analysis should be representative of the typical workload.  The recommendations are based on what DTA sees in the workload.  If the workload is very limited the recommendations may not be optimal.  Usually the best bet is to capture the workload by running SQL Server Profiler, save the trace to a file, and pass that file on to the DTA.