Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 392

Tuning aWorkload
Tuning a whole workload is just as easy. The biggest problem with tuning a workload is creating the
trace file of the workload in the first place, as you need to determine what to put into the trace file. In the
sample scenario, there are only five different queries. Even if they are called in very different patterns,
DTA doesn't really care about how frequently the statement is called; it's just looking for queries that
it can tune. To create a workload file to tune, all you need to do is gather one example of each of your
queries and pass that to DTA, and it will provide recommendations for the whole workload.
To see how this works, start from the point where you have a workload trace file — in this case named
workload.trc . Open DTA and change the name of the session to represent what you are doing. Call this
trace session Mixed Workload with No indexes , indicating that you are starting out with no indexes in
the database.
Select the file that contains the trace file you previously created and then select the tuning options you
want. In this case, you are only interested in new indexes. After selecting the database to tune, start the
analysis. In this sample scenario, DTA came back with the recommendations shown in Figure 11-13.
Figure 11-13
A note of caution when tuning a workload: DTA will make recommendations based upon the cost esti-
mates for each part of the workload. In a workload with a variety of queries, DTA will focus on the big