SYSK 279: What’s New in the World of Database Tuning?

  • Article

Did you know that SQL 2005 Database Engine Tuning Advisor (replacement for SQL 2000 Index Tuning Wizard) can do its analysis using production server’s hardware configuration information, metadata and statistics, but perform the actual tuning on a test server? And it does this without copying the actual business data (it only copies the metadata and necessary statistics)!

 

Some of the new features included with DTA for fine-tuning SQL Server 2000 are:

  1. Time bound tuning – you can control how much time the Database Engine Tuning Advisor spends analyzing a workload. Note: the quality of the recommendations improves as the amount of time increases.
  2. Tune across multiple databases
  3. Tune a broader class of events and triggers, including user-defined functions (UDF), batches that reference temporary tables, statements in triggers
  4. 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.
  5. The Database Engine Tuning Advisor supports what-if analysis, which allows you to specify a configuration of existing and hypothetical physical design structures in an XML input file, and DTA will evaluate the effects of these physical design structures without incurring the overhead of implementing the configuration before you begin tuning.
  6. DTA supports greater variety of tuning options, e.g. you can specify that Database Engine Tuning Advisor consider adding non-clustered indexes or keep all existing clustered indexes when it generates a recommendation.
  7. XML support – DTA can take an XML file as input or generate recommendations in XML. A Database Engine Tuning Advisor XML schema is published and available at the following URL: http://schemas.microsoft.com/sqlserver/

 

Database Engine Tuning Advisor provides the following new tuning features in addition to those that are listed for SQL Server 2000:

 

  1. Partitioning recommendations: Database Engine Tuning Advisor can also recommend partitioning when appropriate to improve performance and manageability of large tables.

 

  1. 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.

 

  1. Members of the db_owner fixed database role can tune their databases: 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.

 

Special thanks to Saleem Hakani for bringing this information to my attention!