Database Reference
In-Depth Information
Table
25-3
summarizes the output of
STATISTICS IO
.
Table 25-3.
Breaking Down the Output from STATISTICS IO
Table
Logical Reads
Person.Employee
2,992
Production.Product
2,992
Purchasing.PurchaseOrderDetail
1,539
Purchasing.PurchaseOrderHeader
44
Person.Person
3
Usually, the sum of the reads from the individual tables referred to in a query will be less than the total number
of reads performed by the query. This is because additional pages have to be read to access internal database objects,
such as
sysobjects
,
syscolumns
, and
sysindexes
.
Table
25-4
summarizes the output of
STATISTICS TIME
.
Table 25-4.
Breaking down the Output from STATISTICS TIME
Event
Duration
CPU
Compile
128 ms
0 ms
Execution
1313 ms
62 ms
Completion
1442 ms
62 ms
Don't use the logical reads in isolation from the execution times. You need to take all the measures into account
when determining poorly performing queries. Conversely, don't assume that the execution time is a perfect measure,
either. Resource contention plays a big part in execution time, so you'll see some variation in this measure. Use both
values, but use them with a full understanding that either in isolation may not be an accurate reflection of reality.
Once the worst-performing query has been identified and its resource use has been measured, the next
optimization step is to determine the factors that are affecting the performance of the query. However, before you do
this, you should check to see whether any factors external to the query might be causing that poor performance.
Analyzing and Optimizing External Factors
In addition to factors such as query design and indexing, external factors can affect query performance. Thus, before
diving into the execution plan of the query, you should analyze and optimize the major external factors that can affect
query performance. Here are some of those external factors:
•
The connection options used by the application
•
The statistics of the database objects accessed by the query
•
The fragmentation of the database objects accessed by the query
