Share via

How to: Implement Tuning Recommendations

  • Article

You can implement the Database Engine Tuning Advisor recommendations manually or automatically as part of the tuning session. If you want to examine the tuning results first before implementing them, use the Database Engine Tuning Advisor graphical user interface (GUI). Then you can use Microsoft SQL Server Management Studio to manually run the Transact-SQL scripts that Database Engine Tuning Advisor generates as a result of analyzing a workload to implement the recommendations. If you do not need to examine the results before implementing them, you can use the -a option with the dta command prompt utility, which causes the utility to automatically implement the tuning recommendations after it analyzes your workload. The following procedures explain how to use both Database Engine Tuning Advisor interfaces to implement tuning recommendations.

To manually implement tuning recommendations with the Database Engine Tuning Advisor GUI

  1. Tune a database using the Database Engine Tuning Advisor GUI or the dta command prompt utility. See How to: Tune a Database. If you want to use an existing tuning session, skip this step and proceed to Step 2.

  2. Start the Database Engine Tuning Advisor GUI. See Starting Database Engine Tuning Advisor. If you want to implement tuning recommendations for an existing tuning session, open it by double-clicking the session name in Session Monitor.

  3. After the new tuning session has finished, or after the tool has loaded the existing session, click Apply Recommendations on the Actions menu.

  4. In the Apply Recommendations dialog box choose from Apply now or Schedule for later. If you choose Schedule for later, select the appropriate date and time.

  5. Click OK to apply the recommendations.

To automatically implement tuning recommendations using the dta command prompt utility

  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.

    Keep the following considerations in mind before you begin tuning:

    • When using a trace table as a workload, that table must exist on the same server where Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, move it to the server where Database Engine Tuning Advisor is tuning.
    • If a tuning session continues running longer than you had anticipated it would run, you can press CTRL+C to end the tuning session. Pressing CTRL+C under these circumstances forces dta to produce the best recommendation possible based on how much of the workload it has consumed, and does not waste the time that the tool has already used to tune the workload.

  2. From a command prompt, enter the following:

    dta -E -D DatabaseName -if WorkloadFile -s SessionName -a

    where -E specifies that your tuning session uses a trusted connection (instead of a login ID and password), -D specifies the name of the database you want to tune or a comma-delimited list of multiple databases that the workload uses, -if specifies the name and path to a workload file, -s specifies a name for your tuning session, and -a specifies that you want the dta command prompt utility to automatically apply the tuning recommendations after the workload is analyzed without prompting you. For more information about using the dta command prompt utility to tune databases, see How to: Tune a Database by Using the dta Utility.

  3. Press ENTER.

Security

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