Professional SQL Server® 2005 Performance Tuning

5.7. Correlating a Profiler Trace with System Monitor Performance Counter Data

Prior to SQL Server 2005, there were no available tools or utilities to correlate SQL Trace event data with Windows System Performance (Perfmon) counter data. However, SQL Server 2005 has added a new feature that correlates these two sources of performance data and displays them in Profiler. All you need to do is to load these two sources of data; Profiler will synchronize these two sources of data via timestamps. Using this new feature, you can evaluate query performance with system resource utilizations visually.

Once Profiler trace and System Monitor performance data are colleted, launch SQL 2005 Profiler.

Open trace file from Profiler and load event data (see Figure 5-32).

From Profiler, import System monitor performance data and select system performance counters to load (see Figure 5-33).

After all data is loaded, the system performance data will be displayed in the middle section of the Profiler.

By clicking a trace event, the corresponding performance data will be highlighted by a vertical line. This is useful when you investigate a long-running query and want to find out how this query affects system resources. Conversely, by clicking on any place on the performance counter graphs, the corresponding trace event will be highlighted.

Performance counter data can be zoomed in and out; this allows you to see system resource utilizations for a narrowed period or an entire period (see Figure 5-34 ...