Database Tuning Advisior

Database Engine Tuning Advisor analyzes a workload and the physical implementation of one or more databases. A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. Database Engine Tuning Advisor uses trace files, trace tables, or Transact-SQL scripts as workload input when tuning databases. 

The Database Engine Tuning Advisor can:

• Recommend the best mix of indexes for databases by using the query optimizer to analyze queries in a workload.
• Recommend aligned or non-aligned partitions for databases referenced in a workload.
• Recommend indexed views for databases referenced in a workload.
• Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload.
• Recommend ways to tune the database for a small set of problem queries.
• Allow you to customize the recommendation by specifying advanced options such as disk space constraints.
• Provide reports that summarize the effects of implementing the recommendations for a given workload.
• Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for Database Engine Tuning Advisor to evaluate.

      DTA new features

• Handles batches that reference transient tables such as temp tables.
• Does not terminate tuning because it cannot parse an event. Instead, it logs the event into the tuning log and then proceeds with tuning other events.
• Parses and tunes queries referencing user-defined functions.
• Handles all USE statements in a trace, which is essential when tuning multiple databases.
• Uses the LoginName column in the workload (when available in trace workloads) to correctly tune the event in the context of the user who executes it.
• Tunes statements in triggers.