Query Statistics History
The most common source of performance problems
in SQL Server is poorly written code. Gathering information on the
performance metrics of queries is a vital part of performance
monitoring and tuning. Opening the Query Statistics History report will
display a window similar to Figure 15.
Figure 15. Query Statistics History report
At the top of the report is the (by now
familiar) time control so that you can determine the time period you’re
interested in viewing. The graph shows queries ordered by different
criteria, such as CPU, Duration, Total I/O, Physical Reads, and Logical
Writes. Clicking any one of these will change the display to show the
top ten queries for that particular metric. In Figure 15,
Duration has been selected, so the queries are ordered by the total
duration of the query for the time period selected. This ability makes
this tool incredibly useful because each of these metrics represents a
possible bottleneck in your system. So, you may be seeing a number of
waits on your system on the CPU in the Server Activity History report.
If you then open the Query Statistics History report and sort by CPU,
you can see the top ten worse offenders and begin to tune the queries
(query tuning is covered in a later section called “Tuning Queries”).
Clicking one of the queries will open the Query Details report, shown in Figure 16. At the top of the report is the text of the query. Depending on what was run inside the query, this can be quite long. Figure 16 shows only the bottom half of the report.
Figure 16. Query Details report, bottom half
The Query Details report shows a lot of information about the query selected. Starting at the top of Figure 16,
you can see the Query Execution Statistics area. This includes various
information such as the average CPU per execution, the average
executions per minute, or the total number of executions for the time
period. All this information provides you with details to enable you to
understand the load that this particular query places on the system.
The graph in the middle of Figure 16
shows data about the different execution plans that have been created
for this query. Selecting each of the different rankings will reorder
the execution plans just as it did the queries in the Query Statistics
History report. Execution plans are the way that SQL Server figures out
how to perform the actions you’ve requested in the query. They’re
covered in more detail in the section “Understanding Execution Plans.”
Being able to look at the information in the Query Details report for a
query and compare it to previous entries will be a powerful tool when
you begin to tune queries.
