Database Tuning Process

Database Engine Tuning Advisor

Database Engine Tuning Advisor is a client physical database design tuning tool that you can run either from a graphical user interface (GUI) or from a command prompt executable file, the dta utility. Database Engine Tuning Advisor analyzes workloads in the form of a file or table that contains Transact-SQL statements. For information about creating workload files and tables.

 

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.

 How to: Create Workloads

A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. The Database Engine Tuning Advisor graphical user interface (GUI) and the dta command-line utility use trace files, trace tables, or Transact-SQL scripts as workload input when tuning databases. Workloads can also be embedded in an XML input file, where you can also specify a weight for each event. For more information about specifying inline workloads, see Using an XML Input File for Tuning.

You can use the Query Editor in Microsoft SQL Server Management Studio or your favorite text editor to create Transact-SQL script workloads. To create trace file or trace table workloads, use SQL Server Profiler. Database Engine Tuning Advisor analyzes these workloads to recommend indexes or partitioning strategies that will improve your server's query performance.

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, then move it to the server where Database Engine Tuning Advisor is tuning.

 To create Transact-SQL script workloads by using SQL Server Management Studio Query Editor

1. Launch the Query Editor in SQL Server Management Studio. For information about how to perform this step, see Editing Scripts and Files in SQL Server Management Studio.
2. Type your Transact-SQL script into the Query Editor. This script should contain a set of Transact-SQL statements that execute against the database or databases that you want to tune. Save the file with an .sql extension. The Database Engine Tuning Advisor GUI and the command-line utility can use this Transact-SQL script as a workload.

 To create trace file and trace table workloads by using SQL Server Profiler

1. Launch SQL Server Profiler by using one of the following methods:
• On the Start menu, point to All Programs, Microsoft SQL Server 2005, Performance Tools, and then click SQL Server Profiler.
• In SQL Server Management Studio, click the Tools menu, and then click SQL Server Profiler.
2. Create a trace file or table as described in the following procedures that uses the SQL Server Profiler Tuning template:
• How to: Create a Trace (SQL Server Profiler)
• How to: Save Trace Results to a File (SQL Server Profiler)
  Database Engine Tuning Advisor assumes that the workload trace file is a rollover file. For more information about rollover files, see Limiting Trace File and Table Sizes.
• How to: Save Trace Results to a Table (SQL Server Profiler)
  Make sure that tracing has stopped before using a trace table as a workload.

We recommend that you use the SQL Server Profiler Tuning template for capturing workloads for Database Engine Tuning Advisor.

If you want to use your own template, ensure that the following trace events are captured for the version of SQL Server that you are using.

SQL Server 2005:

• RPC:Completed
• SQL:BatchCompleted
• SP:StmtCompleted

SQL Server 2000:

• RPC:Completed
• SQL:BatchCompleted

You can also use the Starting versions of these trace events. For example, SQL:BatchStarting. However, the Completed versions of these trace events include the Duration column, which allows Database Engine Tuning Advisor to more effectively tune the workload. Database Engine Tuning Advisor does not tune other types of trace events. For more information about these trace events, see Stored Procedures Event Category and TSQL Event Category. For information about using the SQL Trace stored procedures to create a trace file workload, see How to: Create a Trace (Transact-SQL).

 Trace File or Trace Table Workloads that Contain the LoginName Data Column

Database Engine Tuning Advisor submits Showplan requests as part of the tuning process. When a trace table or file that contains the LoginName data column is consumed as a workload, Database Engine Tuning Advisor impersonates the user specified in LoginName. If this user has not been granted the SHOWPLAN permission, which enables the user to execute and produce Showplans for the statements contained in the trace, Database Engine Tuning Advisor will not tune those statements. For more information about the LoginName data column, see Describing Events by Using Data Columns. For more information about the SHOWPLAN permission, see Showplan Security.

 To avoid granting the SHOWPLAN permission to each user specified in the LoginName column of the trace

1. Tune the trace file or table workload. For more information, see How to: Tune a Database.
2. Check the tuning log for statements that were not tuned due to inadequate permissions. For more information, see About the Tuning Log and How to: View Tuning Output.
3. Create a new workload by deleting the LoginName column from the events that were not tuned, and then save only the untuned events in a new trace file or table. For more information about deleting data columns from a trace, see How to: Specify Events and Data Columns for a Trace File (SQL Server Profiler) or How to: Modify an Existing Trace (Transact-SQL).
4. Resubmit the new workload without the LoginName column to Database Engine Tuning Advisor.

Database Engine Tuning Advisor will tune the new workload because login information is not specified in the trace. If the LoginName does not exist for a statement, Database Engine Tuning Advisor tunes that statement by impersonating the user who started the tuning session (a member of either the sysadmin fixed server role or the db_owner fixed database role

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.

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.

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.

How to: View Tuning Output

When the Database Engine Tuning Advisor tunes databases, it creates summaries, recommendations, reports, and tuning logs. You can use the tuning log output to troubleshoot Database Engine Tuning Advisor tuning sessions. You can use the summaries, recommendations, and reports to determine whether you want to implement tuning recommendations or continue tuning until you achieve the query performance improvements that you need for your Microsoft SQL Server 2005 installation. The following procedures describe how to view tuning recommendations, summaries, reports, and tuning logs with the Database Engine Tuning Advisor graphical user interface (GUI). You can also use the GUI to view tuning output that is generated by the dta command-line utility. For a step-by-step guided tour through these two interfaces of Database Engine Tuning Advisor, see Database Engine Tuning Advisor Tutorial.

If you use the dta command-line utility and specify that output be written to an XML file by using the -ox argument, you can open and view the XML output file by clicking Open File on the File menu of SQL Server Management Studio. For more information, see Introducing SQL Server Management Studio. For information about the dta command-line utility, see dta Utility.

 To view tuning recommendations with the Database Engine Tuning Advisor GUI

1. Tune a database using the Database Engine Tuning Advisor GUI or the dta command-line 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 view tuning recommendations for an existing tuning session, open it by double-clicking the session name in the Session Monitor window.

After the new tuning session has finished, or after the tool has loaded the existing session, the Recommendations page is displayed.

3. On the Recommendations page, click Partition Recommendations and Index Recommendations to view panes that display the tuning session results. If you did not specify partitioning when you set the tuning options for this session, the Partition Recommendations pane is empty.
4. In either the Partition Recommendations or the Index Recommendations pane, use the scroll bars to view all the information displayed in the grid.
5. Uncheck Show existing objects at the bottom of the Recommendations tabbed page. This causes the grid to display only those database objects that are referenced in the recommendation. Use the bottom scroll bar to view the right-most column in the recommendations grid, and click an item in the Definition column to view or copy the Transact-SQL script that creates that object in your database.
6. If you want to save all of the Transact-SQL scripts that create or drop all database objects in this recommendation into one script file, click Save Recommendations on the Actions menu.

 To view the tuning summary and reports with the Database Engine Tuning Advisor GUI

1. Tune a database using the Database Engine Tuning Advisor GUI or the dta command-line 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 view tuning summaries and reports for an existing tuning session, open it by double-clicking the session name in the Session Monitor.
3. After the new tuning session has finished, or after the tool has loaded the existing session, click the Reports tab.
4. The Tuning Summary pane contains information about the tuning session. The information provided by the Expected percentage improvement and the Space used by recommendation items can be especially useful to decide whether you want to implement the recommendation.
5. In the Tuning Reports pane, click Select report to choose a tuning report to view. See Choosing a Database Engine Tuning Advisor Report for information about these reports.

 To view tuning logs with the Database Engine Tuning Advisor GUI

1. Tune a database using the Database Engine Tuning Advisor GUI or the dta command-line utility. Make sure that you check Save tuning log on the General tab when you tune the workload. 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 view tuning summaries and reports for an existing tuning session, open it by double-clicking the session name in the Session Monitor window.
3. After the new tuning session has finished, or after the tool has loaded the existing session, click the Progress tab. The Tuning Log pane displays the contents of the log. The log contains information about workload events that Database Engine Tuning Advisor was unable to analyze. For information about how you can use the tuning log contents to troubleshoot a tuning session, see Troubleshooting Database Engine Tuning Advisor.

If all events in the tuning session were analyzed by Database Engine Tuning Advisor a message indicating that the tuning log is empty for the session displays. If Save tuning log was not checked on the General tab when the tuning session was originally run, a message displays indicating that.

How to: Perform Exploratory Analysis

The user-specified configuration feature of Database Engine Tuning Advisor enables database administrators to perform exploratory analysis. Using this feature, database administrators specify a desired physical database design to Database Engine Tuning Advisor, and then they can evaluate the performance effects of that design without implementing it. User-specified configuration is supported by both the Database Engine Tuning Advisor graphical user interface (GUI) and the command-line utility. However, the command-line utility provides the greatest flexibility.

If you use the Database Engine Tuning Advisor GUI, you can evaluate the effects of implementing a subset of a Database Engine Tuning Advisor tuning recommendation, but you cannot add hypothetical physical design structures for Database Engine Tuning Advisor to evaluate.

For more information about the user-specified configuration feature, see Exploratory Analysis Using Database Engine Tuning Advisor.

The following procedures explain how to use the user-specified configuration feature with both tool interfaces.

 Using Database Engine Tuning Advisor GUI to Evaluate Tuning Recommendations

The following procedure describes how to evaluate a recommendation that is generated by Database Engine Tuning Advisor, but the GUI does not enable you to specify new physical design structures for evaluation.

 To evaluate tuning recommendations with the Database Engine Tuning Advisor GUI

1. Use the Database Engine Tuning Advisor GUI to tune a database. (See How to: Tune a Database by Using Database Engine Tuning Advisor.) If you want to evaluate an existing tuning session, double-click it in Session Monitor.
2. On the Recommendations tab, clear the recommended physical design structures that you do not want to use.
3. On the Actions menu, click Evaluate Recommendations. A new tuning session is created for you.
4. Type the new Session name. To view the physical database design structure configuration that you are evaluating, choose Click here to see the configuration section, in the Description area at the bottom of the Database Engine Tuning Advisor application window.
5. Click the Start Analysis button on the toolbar. When Database Engine Tuning Advisor is finished, you can view the results on the Recommendations tab.

 Using Database Engine Tuning Advisor GUI to Export Tuning Session Results for "What-if" Tuning Analysis

The following procedure describes how to export Database Engine Tuning Advisor tuning session results to an XML file, which you can edit, and then tune it with the dta command-line utility. This enables you to perform tuning analysis on hypothetical new physical design structures without incurring the overhead of implementing them in your database before you find out whether they produce the performance improvements that you need. Using the Database Engine Tuning Advisor GUI to initially tune your database and then exporting the tuning results to an .xml file is a good way for users who are new to XML to use the flexibility of the Database Engine Tuning Advisor XML schema to perform "what-if" analysis.

 To export tuning session results from the Database Engine Tuning Advisor GUI for "what-if" analysis with the dta command-line utility

1. Use the Database Engine Tuning Advisor GUI to tune a database. See How to: Tune a Database by Using Database Engine Tuning Advisor. If you want to evaluate an existing tuning session, double-click it in the Session Monitor.
2. On the File menu, click Export Session Results and save it as an XML file.
3. Open the XML file created in Step 2 in your favorite XML editor, text editor, or in Microsoft SQL Server Management Studio. Scroll down to the Configuration element. Copy and paste the Configuration element section into an XML input file template after the TuningOptions element. Save this XML input file.
4. In the new XML input file that you created in Step 3, specify any tuning options you want in the TuningOptions element, edit the Configuration element section (add or delete the physical design structures as appropriate for your analysis), save the file, and validate it against the Database Engine Tuning Advisor XML schema. For information about editing this XML file, see XML Input File Reference (DTA).
5. Use the XML file that you created in Step 4 as input to the dta command line utility. For information about using XML input files with this tool, see How to: Tune a Database by Using the dta Utility.

 Using the User-specified Configuration Feature with the dta Command Line Utility

If you are an experienced XML developer, you can create a Database Engine Tuning Advisor XML input file in which you can specify a workload and a hypothetical configuration of physical database design structures, such as indexes, indexed views, or partitioning. Then you can use the dta command-line utility to analyze the effects this hypothetical configuration has on query performance for your database. The following procedure explains this process step by step:

 To use the user-specified configuration feature with the dta command line utility

1. Create a tuning workload. For information about performing this task, see How to: Create Workloads.
2. Copy and paste the XML Input File Sample with User-specified Configuration (DTA) into your XML editor or a text editor. Use this sample to create an XML input file for your tuning session. For information about performing this task, see How to: Create XML Input Files.
3. Edit the TuningOptions and the Configuration elements in the sample XML input file. In the TuningOptions element, specify what physical design structures you want Database Engine Tuning Advisor to consider during the tuning session. In the Configuration element, specify the physical design structures that match the hypothetical configuration of physical database design structures that you want Database Engine Tuning Advisor to analyze. For information about what attributes and child elements you can use with the TuningOptions and the Configuration parent elements, see XML Input File Reference (DTA).
4. Save the input file with an .xml extension.
5. Validate the XML input file you saved in Step 4 against the Database Engine Tuning Advisor XML schema. This schema is installed at the following location when you install Microsoft SQL Server 2005:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2004\07\dta\dtaschema.xsd

The Database Engine Tuning Advisor XML schema is also available online at http://schemas.microsoft.com/sqlserver/2004/07/dta.

6. After creating a workload and an XML input file, you are ready to submit the input file to the dta command-line utility for analysis. Make sure that you specify an XML output file name for the -ox utility argument. This creates an XML output file with a recommended configuration specified in the Configuration element. If you want to run Database Engine Tuning Advisor again to check another hypothetical configuration that is based on the output, you can copy and paste the Configuration element contents from the output file into a new or your original XML input file. For information about using an XML input file with the dta utility, see the procedure "To tune a database using an XML input file" in How to: Tune a Database by Using the dta Utility.

After tuning is finished, either use the Database Engine Tuning Advisor GUI to view the tuning reports, or open the XML output file to view the TuningSummary and the Configuration elements to view the Database Engine Tuning Advisor recommendations. For information about viewing the results of your tuning session, see How to: View Tuning Output. Also note that the XML output file may contain Database Engine Tuning Advisor analysis reports.

7. Repeat steps 6 and 7 until you create the hypothetical configuration that produces the query performance improvement that you need. Then you can implement the new configuration. See How to: Implement Tuning Recommendations.

How to: Implement Tuning Recommendations

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