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

As Figure 10-10 shows, highlighted in the result window, the user transaction named PURCHASE used
more system resources than any other transaction. The total PURCHASE transaction count 15,486 was
about 17% (15486 92835) of the total user transactions in the workload. This PURCHASE transaction

used about 52% (516,640

÷

÷ 988,086) of total CPU, and 77% ((8,939,662 + 2025) ÷ (11,540,281 + 2197)) of disk

I/O resources.

The next query reports average resource utilization by transaction, and Figure 10-11 shows the results.
Note that the workload was collected from the SQL 2000 trace provider, and the duration is in millisec-
onds. As a reminder, in SQL 2005 trace provider, the duration measurement is in microseconds (one
millisecond equals 1,000 microseconds). Since most DBAs are used to millisecond measurements in the
duration column, it is a good idea to make your analysis script consistent across SQL 2000 and 2005. In
other words, when you analyze SQL 2005 duration data in a trace file, divide the value by 1,000 to convert
it to milliseconds.

Figure 10-11

-- Data was collected from SQL 2000, the duration is millisecond
SELECT
SUBSTRING(textData, 1, 25) AS TextData,
COUNT(*) AS TransactionCount,
AVG(duration) AS Ave_Duration_ms,
AVG(cpu) AS Average_CPU,
AVG(reads) AS Average_Reads,
AVG(writes) AS Average_Writes
FROM fn_trace_gettable('c: \ Temp \ DS2_40Threads_20Min_3SecThink.trc', default)