• Article

Tuning a Database

You can use workloads to tune databases either through the Database Engine Tuning Advisor graphical user interface (GUI) or the dta command-line utility. A workload is a set of Transact-SQL statements that executes 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.

Regardless of the interface you choose, using a workload to tune a database involves the same overall process. The following list presents the workload tuning tasks in the order they are performed and provides links to the appropriate how-to topics.

Tuning Workload Process

  1. How to: Create Workloads

    Before you can tune any database, you must create a Transact-SQL script file, trace file, or trace table that contains a representative workload for the databases you want to tune.

  2. How to: Tune a Database

    Before you start tuning your databases, you must determine which database features (indexes, indexed views, partitioning strategies) you want Database Engine Tuning Advisor to consider. Then, you need to determine which Database Engine Tuning Advisor user interface best suits your tuning needs and your skill level. The GUI is the best choice for users who are not highly experienced in physical database design structures. The dta command-line utility is the best choice for experienced database administrators who want the flexibility that the Database Engine Tuning Advisor XML input file provides, or who want to incorporate Database Engine Tuning Advisor tuning functionality into scripts or use it with their favorite XML database design tools.

  3. How to: View Tuning Output

    Database Engine Tuning Advisor creates tuning logs, tuning summaries, recommendations, and reports that you can use to evaluate the results of tuning sessions. Using this information, you can decide whether you are finished tuning and ready to implement the Database Engine Tuning Advisor recommendation.

  4. How to: Perform Exploratory Analysis

    This is an optional step. If you review the Database Engine Tuning Advisor output and decide you would like to tune further to determine whether you can reach a more optimal configuration, use the new user-specified configuration feature of Database Engine Tuning Advisor. This feature lets you specify a hypothetical configuration for Database Engine Tuning Advisor to analyze without incurring the overhead of implementing the hypothetical configuration first.

  5. How to: Implement Tuning Recommendations

    After you are satisfied that you have the best configuration for your installation of Microsoft SQL Server, you are ready to implement it for testing before moving it to your production environment.