Database engine Tuning Advisor (DTA)
Database Engine Tuning Advisor in SQL Server 2005 replaces ITW (Index Tuning Wizard). Database Tuning Advisor has much improved and provides very good recommendations. Physical design structures now include: Clustered Indexes, Non-clustered indexes, Indexed views and Partitioning
It now has two interfaces one GUI and the other one Command line interface called DTA.exe which allows you to use DTA functionality in applications and scripts.
DTA can be used to fine-tune both SQL Server 2000 & SQL Server 2005 databases. Note:-Some of the SQL 2000 options are not supported.. please refer to BOL for more information.
Some of the new features included with DTA for fine-tuning SQL Server 2000 are:
- You can control how much time the Database Engine Tuning Advisor spends analyzing a workload. The quality of the recommendations improves as the amount of time increases.
- You can include workloads with: User-defined functions, batches that reference temporary tables, statements in triggers.
- Tune across multiple databases: You can tune a workload that involves multiple databases.
- Tuning log: Database Engine Tuning Advisor writes to the tuning log all events that it cannot tune and provides a message that gives a reason why the event could not be tuned. You can view the log during the tuning session to determine whether Database Engine Tuning Advisor can tune the events in your workload.
- What-if analysis: The Database Engine Tuning Advisor user-specified configuration feature supports what-if analysis. The user-specified feature allows you to specify a configuration of existing and hypothetical physical design structures in an XML input file. Then you can use Database Engine Tuning Advisor to evaluate the effects of these physical design structures without incurring the overhead of implementing the configuration before you begin tuning.
- More control over tuning options: Database Engine Tuning Advisor allows you to specify a greater variety of tuning options. For example, you can specify that Database Engine Tuning Advisor consider adding nonclustered indexes or keep all existing clustered indexes when it generates a recommendation.
- XML support: Database Engine Tuning Advisor can take an XML file as input or generate recommendations in XML.
Tune SQL Server 2005 using DTA:
If you want to tune SQL Server 2005 databases, Database Engine Tuning Advisor provides the following new tuning features in addition to those that are listed for SQL Server 2000 above:
- Partitioning recommendations: Database Engine Tuning Advisor can also recommend partitioning when appropriate to improve performance and manageability of large tables.
- Supports using a test server to reduce the production server tuning load: Database Engine Tuning Advisor tunes a database on a production server by offloading most of the tuning load onto a test server. It does this by using the production server hardware configuration information and without actually copying the data from the production server to the test server. Database Engine Tuning Advisor does not copy actual data from the production server to the test server. Instead, it only copies the metadata and necessary statistics.
- In addition to members of the sysadmin fixed server role, members of the db_owners fixed database role can use Database Engine Tuning Advisor to tune databases that they own.
