Finding Problem Queries with SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 137

Case Summary
To summarize our activities:

❑ You used SQL Profiler as a code generator to generate a server-side trace.

❑ You made minor code modifications: reset a stop time, specified a unique output file name, and

specified a set of output rollover files to a maximum of ten files.

❑ Tested the server-side trace and scheduled it.

❑ Simulated database events.

❑ After the trace stopped, you used a pre-prepared T-SQL script to analyze your trace data.

Based on trace data you collected by measuring CPU usage per single or multiple execution, the result
indicates that the stored procedure marriage is the most costly query.

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). With the integrated data in Profiler, you can
quickly pinpoint a problem area for detailed troubleshooting or a root-cause analysis.