Database Reference
In-Depth Information
Overall Resource Use
The overall resource use of the query provides a gross figure for the amount of hardware resources consumed by the
worst-performing query. You can compare the resource use of an optimized query to the overall resource use of a
nonoptimized query to ensure the overall effectiveness of the performance techniques you've applied.
You can determine the overall resource use of the query from the workload trace. You'll use the first call of
the procedure since it displays the worst behavior. Table
25-2
shows the overall use of the query from the trace
in Figure
25-1
. One point, the durations in the table are in milliseconds, while the values in Figure
25-1
are in
microseconds. Remember to take this into account when working with Extended Events.
Table 25-2.
Data Columns Representing the Amount of Resources Used by a Query
Data Column
Value
Description
LogicalReads
7570
Number of logical reads performed by the query. If a page is not found in memory,
then a logical read for the page will require a physical read from the disk to fetch the
page to the memory first.
Writes
0
Number of pages modified by the query.
CPU
31 ms
How long the CPU was used by the query.
Duration
175.1 ms
The time it took SQL Server to process this query from compilation to returning the
result set.
■
in your environment, you may have different figures for the preceding data columns. irrespective of the
data columns' absolute values, it's important to keep track of these values so that you can compare them with the
corresponding values later.
Note
Detailed Resource Use
You can break down the overall resource use of the query to locate bottlenecks on the different database tables
accessed by the query. This detailed resource use helps you determine which table accesses are the most problematic.
Understanding the wait states in your system will help you identify where you need to focus your tuning. A rough rule
of thumb can be to simply look at duration; however, duration can be affected by so many factors that it's an imperfect
measure, at best. In this case, I'll spend time on all three: CPU usage, reads, and duration. Reads are a popular
measure of performance, but they can be as problematic to look at in isolation as duration. This is why I spend time
on all the values.
As you saw in Chapter 6, you can obtain the number of reads performed on the individual tables accessed by
a given query from the
STATISTICS IO
output for that query. You can also set the
STATISTICS TIME
option to get
the basic execution time and CPU time for the query, including its compile time. You can obtain this output by
reexecuting the query with the
SET
statements as follows (or by selecting the Set Statistics IO check box in the query
window):
--not to be run in production
DBCC FREEPROCCACHE();
DBCC DROPCLEANBUFFERS;
GO
