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