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

We will use the workload collected from the DS2 application mentioned earlier and three servers for this
study:

TestServerH: This is the target server where we have the DS2 database installed. Multiple Pro-
filer replay sessions will be connected to this server. The server capacity is planned for consoli-
dating workloads from other SQL servers. A full database backup of DS2 has been prepared to
reset the database to a proper state. It uses Intel Pentium Xeon 3.6 GHz 4-Proc, 6 GB RAM with
SQL Server 2005 ( x 64-bit).

❑

❑ TestServerG: This server is dedicated for SQL Profiler (profiler90.exe) to drive the workloads. It

has the same server configuration as TestServerH. It uses: Intel Pentium Xeon 3.6 GHz 4-Proc, 6
GB RAM with SQL Server 2005 ( x 64-bit).

TestServerM: This server is mainly used for collecting performance data from the TestServerH.
It has a 32-bit SQL Server 2005 installed as an optional server to drive the workload in addition to
the TestServerG. We will use Windows 2003 System Performance (Perfmon) and SQL 2005 Pro-
filer tools to collect performance data. It uses: Intel Pentium III Xeon 700 MHz; 4 processors; 3.5
GB of RAM; SQL Server 2005 Enterprise SP1.

❑

We replayed the same workload (DS2_40Threads_20 Min_3SecThink.trc) from each of the SQL Profiler
connections (from both TestServerG and TestServerM) to the target server TestServerH with five-second
separations. All workload replay sessions were finished within 17 minutes.

Table 10-4 shows individual workload replay performance:

Table 10-4: Workload Replay Performance Measurement

Workload Replayed from Profiler Instance Replay Time

TestServerG 1 12 min 17 sec 700 ms

TestServerG 2 12 min 6 sec 637 ms

TestServerG 3 12 min 27 sec 231 ms

TestServerG 4 16 min 55 sec 739 ms

TestServerM 1 15 min 46 sec 11 ms

We will look at the target server TestServerH performance from a system resource point of view and a
SQL query performance point of view, and then summarize results.

The following figures show the system resource point of view:

❑ CPU Utilization: We collected the system counter, % Processor Time, for the total instance

(_Total) from the target server. Figure 10-24 indicates that during the workload replay, the
processor time persisted between 60 and 80 percent.