Report Description: Profiler Trace Analysis
The purpose of the Profiler Trace Analysis
top-level report is to graph the rate of query execution and the amount of CPU
and I/O used over time, and to identify the most expensive queries in your
workload.
Use the graph of query execution statistics
at the top of the report to identify which type of activity appears to be the
bottleneck in your system. Then use the Expensive Queries list at the bottom
of the report to identify the query that is using the most of that resource.
For example, suppose that you had imported a profiler trace that was captured
when your server was CPU-bound. You would sort the Expensive Queries list by
CPU use by clicking the sort icon on the CPU column header (click the icon twice
to sort in descending order). Once you have identified the offending query,
click the query text to drill into a similar report showing the execution
statistics for that particular query. In the child report you can see whether
the rate of execution of the query increased, or whether the CPU cost per
execution changed, for example due to a different query plan.
Other comments:
- Each 1000 CPU (ms)/sec represents 100%
CPU utilization for one logical processor (hyperthreading processors or
processors with multiple cores may expose more than one logical processor
per socket). If you have a 4 processor system, for example, a query that
used an average of 2000 ms of CPU time each second would have be
responsible for an overall system CPU utilization rate of 50%.
- One of the primary advantages of this
report is that it shows the total cost of a query across all executions of
queries with the same form. The Profiler utility that is included with
SQL Server allows you to order a trace to identify the individual query
instance that consumed the most CPU time, but it is very often the case
that the most expensive query on a system is a relatively quick, but
frequently-executed, query. For example, a query that consumes just 250ms
of CPU time per execution will completely saturate an 8-processor server
if it is executed more than 30 times each second. It is extremely
difficult to determine what query you should focus on without the type of
aggregated view of query cost that this report provides.
- This report is based on data loaded into
the Diagnostic Data Warehouse by a utility called TraceBuster.exe (located
in the “Collection” directory within the SQL Nexus startup folder). This
utility parses query text and strips out inline query parameter data,
leaving a “template” form of the query that is the same for all executions
of the query.
- If you want to zoom in on a particular
time window within the trace, click the parameters button on the report
toolbar and choose a Start Time and End Time.
- You can use the Profiler Trace Analysis
reports for both live server monitoring and for offline analysis of trace
data captured during a previous problem period. For more information
about the latter scenario, see the Quick Start (Postmortem Analysis)
help topic.