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

Figure 5-31
Best Practice
Process your output trace data on a non-production server. Processing trace events can
be CPU intensive if you use T-SQL to parse and aggregate the trace events collected.
AnalyzingCostlyQueries—SingleExecutionvs.MultipleExecutions
Costly queries can be classified in several ways. For example, most people capture and report the Top
30 (or N) costly queries with high I/O or CPU cost. Analyzing both the single longest query and a query
with multiple executions is advisable.
Queries with multiple executions form an overall database load. It is crucial to analyze load patterns on
a regular basis. If a stored procedure is executed in 200 milliseconds, it may not look bad at all. However,
if this stored procedure is executed two million times a day, you certainly need to pay more attention to
it and optimize it to the max. Compared to a single long-running query, queries with multiple executions
may deserve more attention.
From the example results in Figure 5-31, the stored procedure [marriage] has the greatest overall impact
on the system in terms of accumulated CPU and I/O usages. It has been executed 13 times.
If you evaluate the most costly query based on a single execution, it is [PeopleAndMarriage] .Thisquery
used 9624 milliseconds of CPU and 300119 reads.