Friday, October 17, 2014

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.

No comments:

Post a Comment