Capturing, Measuring, and Replaying a Workload Using SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 343

17.6 percent for the whole workload. By replaying the original workload, the duration time of the stored
procedure actually improved 61 percent.

Scenario 2: Replaying aWorkload inDifferent Environments
andMeasuringOverall Response Time
In this scenario, we are simulating a situation in which we need to upgrade SQL Server 2000 to a newer
version on better hardware configurations. We want to have a quick estimate of the workload improve-
ment on different server configurations.

We will use the workload collected from the DS2 application mentioned earlier. We will replay the work-
load on different servers. The following four servers with different hardware and software configurations
will be used:

❑ TestServerL: This server is equipped with the same hardware and software as the server on

which the workload data was collected: Intel Pentium III 662 MHz; 2 processors; 1 GB of RAM;
80 GB RAID 5 locally attached; SQL Server 2000 Enterprise SP4.

❑ TestServerM: This server has a faster processor speed and 4 processors; Intel Pentium III Xeon

700 MHz; 4 processors; 3.5 GB of RAM; 120 GB RAID 5 locally attached; SQL Server 2005 Enter-
prise SP1.

TestServerH: This server has more power compared to the other testing servers. A 64-bit ( x 64)
SQL Server was installed: Intel Pentium Xeon 3.6 GHz; 4 processors; 6 GB of RAM; 450 GB RAID
10 locally attached; SQL Server 2005 x 64 Enterprise SP1.

❑

TestServerG: This server has the same configuration as TestServerH but is solely dedicated for
replaying workloads. Since SQL 2005 Profiler replays trace events at the highest speed possible,
it uses high CPU during the trace replay. For this reason, we will isolate the Profiler load on this
dedicated server.

❑

In SQL 2005 Profiler, there are two main options for replaying trace data (or workload data). The first
option is to play the events in the sequence they were collected. The second option is to play the workload
concurrently using multiple threads.

For this scenario, to get consistent results, the best option is to use a single-threaded sequential
replay. For certain workloads and applications, a multiple-threaded replay can be used if the work-
load has been fully analyzed for event arrival rates and event synchronizations. Figure 10-20 shows
the Replay Configuration options.

Figure 10-21 shows the workload replay on the server TestServerL. This server has an almost identi-
cal hardware and software configuration as the source server from which we collected the workload
performance data.

To summarize the results from TestServerL in Figure 10-21, the SQL Profiler replayed 92,922 events
in 17 minutes 10 seconds. The workload file contained 20 minutes of event executions from the DS2
application. During the replay, even though a single threaded execution was selected, the replay finished
in less than 20 minutes. This demonstrated that the SQL 2005 Profiler replay does not replay events at the
same speed at which the events were collected. We will use this replay time (17 minutes 10 seconds) as a
reference measurement.