10.2. Measuring Workload Performance
A workload collected from a real environment contains time series data with raw performance measurements. The following sections provide examples for measuring overall system resource usage in a workload. A workload replay-time provides a measurement that allows you to compare performance.
10.2.1. Preliminary Analysis of the Workload
When you collect workload data from a SQL Server instance, it is best to collect Windows performance counters at the same time. Please refer to Chapter 2 for applicable and useful counters.
This section provides T-SQL sample scripts to summarize overall users' activities and corresponding system resource consumptions in a workload. All queries retrieve data from the original workload file named DS2_40Threads_20 Min_3SecThink.trc through the T-SQL fn_trace_gettable built-in function.
The source script (176399 Ch10_SourceTSQL_Scripts.sql) is available at www.wrox.com. At the end of this section, summary information is listed in a table.
Most of the user database requests come from two trace events: RPC:Completed and SQL:BatchCompleted. The following query aggregates CPU and I/O usages from these two events. Figure 10-9 shows the results.
Figure 10-9. Figure 10-9
SELECT COUNT(*) AS EventCount, SUM(cpu) AS Total_CPU_MS, SUM(reads) AS Total_Reads, SUM(writes) AS Total_Writes FROM fn_trace_gettable('c:\Temp\DS2_40Threads_20Min_3SecThink.trc', ...Get Professional SQL Server® 2005 Performance Tuning now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
