Share via

  • Article

How to: Tune a Database by Using Database Engine Tuning Advisor

You can use Database Engine Tuning Advisor graphical user interface (GUI) to tune databases by using workload files or tables. The Database Engine Tuning Advisor GUI enables you to easily view results of your current tuning session and results of previous tuning sessions. For more information, see Using Session Monitor to Evaluate Tuning Recommendations.

Note

Make sure that tracing has stopped before using a trace table as a workload for Database Engine Tuning Advisor. Database Engine Tuning Advisor does not support using a trace table to which trace events are still being written as a workload.

To tune a database using a workload file or table as input

  1. Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis. For more information, see About Workloads and Considerations for Using Database Engine Tuning Advisor.

  2. Create a workload. For more information, see How to: Create Workloads

  3. Launch Database Engine Tuning Advisor, and log into an instance of Microsoft SQL Server. For more information, see Starting Database Engine Tuning Advisor.

  4. On the General tab, type a name in Session name to create a new tuning session.

  5. Choose either a Workload File or Table and type either the path to the file, or the name of the table in the adjacent text box.

    The format for specifying a table is

    database_name.schema_name.table_name

    To search for a workload file or table, click the Browse button.

    Database Engine Tuning Advisor assumes that workload files are rollover files. For more information about rollover files, see Limiting Trace File and Table Sizes.

    When using a trace table as a workload, that table must exist on the same server that Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, move it to the server that Database Engine Tuning Advisor is tuning before using it as your workload.

  6. Select the databases and tables against which you wish to run the workload that you selected in step 5. To select the tables, click the Selected Tables arrow.

  7. Check Save tuning log to save a copy of the tuning log. Clear the check box if you do not want to save a copy of the tuning log.

    You can view the tuning log after analysis by opening the session and selecting the Progress tab.

  8. Click the Tuning Options tab and select from the options listed there. For more information, see Available Tuning Options.

  9. Click the Start Analysis button in the toolbar.

    If you want to stop the tuning session after it has started, choose one of the following options on the Actions menu:

    • Stop Analysis (With Recommendations) stops the tuning session and prompts you to decide whether you want Database Engine Tuning Advisor to generate recommendations based on the analysis done up to this point.

    • Stop Analysis stops the tuning session without generating any recommendations.

Note

Pausing Database Engine Tuning Advisor is not supported. If you click the Start Analysis toolbar button after clicking either the Stop Analysis or Stop Analysis (With Recommendations) toolbar buttons, Database Engine Tuning Advisor starts a new tuning session.

Security

For important information about the permissions required to use Database Engine Tuning Advisor, see Initializing Database Engine Tuning Advisor.