• Article

Database Engine Tuning Advisor Overview

Microsoft SQL Server Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views, and partitions without requiring an expert understanding of the structure of the database or the internals of Microsoft SQL Server.

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. You can create Transact-SQL script workloads with the Query Editor in SQL Server Management Studio. You can create trace file and trace table workloads by using the Tuning Template in SQL Server Profiler. For information about using SQL Server Profiler to create traces that you can use as workloads, see Introducing SQL Server Profiler.

After analyzing a workload, Database Engine Tuning Advisor can recommend that you add, remove, or modify physical design structures in your databases. The advisor can also recommend what statistics should be collected to back up physical design structures. The physical design structures include clustered indexes, nonclustered indexes, indexed views, and partitioning. Database Engine Tuning Advisor recommends a set of physical design structures that reduces the query optimizers estimated cost of the workload.

Database Engine Tuning Advisor Tuning Capabilities

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.