Share via

  • Article

About Workloads

To build a recommendation of the optimal set of indexes, indexed views, or partitions for your databases, Database Engine Tuning Advisor requires a workload. A workload consists of a Transact-SQL script or a SQL Server Profiler trace saved to a file or table. If you are using a SQL Server Profiler trace file or table, they must contain Transact-SQL batch or remote procedure call (RPC) event classes, and the Event Class and Text data columns. For more information, see TSQL Event Category.

Database Engine Tuning Advisor is designed to handle the following workload types:

  • Online transaction processing (OLTP) queries only

  • Online analytical processing (OLAP) queries only

  • Mixed OLTP and OLAP queries

  • Query-heavy workloads (more queries than updates)

  • Update-heavy workloads (more updates than queries)

Workloads must contain many tunable events that represent typical workloads that run against your databases.

If you do not have an existing workload for Database Engine Tuning Advisor to analyze, you can create one using the Tuning Template in SQL Server Profiler. The Tuning Template is configured to capture the necessary events and columns for tuning with Database Engine Tuning Advisor. For more information, see SQL Server Profiler Templates.

After the trace has captured a representative sample of normal database activity, Database Engine Tuning Advisor analyzes the workload and then recommends an optimal configuration of indexes, indexed views, or partitions that improve database performance.

You can also use the following as workloads:

  • Benchmarks specific to your organization or your industry.

  • Problem queries that take a long time to run.

Using Workloads

Consider the following when using workloads with Database Engine Tuning Advisor:

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

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

  • When using a trace file as a workload, Database Engine Tuning Advisor assumes it is a rollover file. For more information about rollover files, see Limiting Trace File and Table Sizes.

  • If the workload contains queries that use the USE PLAN query hint, which forces SQL Server to use a specific query plan, Database Engine Tuning Advisor keeps all indexes contained in the forced plan, and will not drop any of those indexes as a part of its tuning recommendation.

  • 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 does not have sufficient permissions to execute and produce Showplans for the statements contained in the trace, Database Engine Tuning Advisor will not tune those statements. To resolve this, remove the LoginName column from those events that are not tuned. For more information, see "Trace File or Trace Table Workloads that Contain the LoginName Data Column" in How to: Create Workloads.