Share via

  • Article

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.

Note

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, 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:

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 and later:

  • 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).