Database Reference
In-Depth Information
STATISTICS IO
As discussed in the “Identifying Costly Queries” section earlier in the chapter, the number of reads in the
Reads
column is frequently the most significant cost factor among
duration
,
cpu
,
reads
, and
writes
. The total number of
reads performed by a query consists of the sum of the number of reads performed on all tables involved in the query.
The reads performed on the individual tables may vary significantly, depending on the size of the result set requested
from the individual table and the indexes available.
To reduce the total number of reads, it will be useful to find all the tables accessed in the query and their
corresponding number of reads. This detailed information helps you concentrate on optimizing data access on
the tables with a large number of reads. The number of reads per table also helps you evaluate the impact of the
optimization step (implemented for one table) on the other tables referred to in the query.
In a simple query, you determine the individual tables accessed by taking a close look at the query. This
becomes increasingly difficult the more complex the query becomes. In the case of stored procedures, database
views, or functions, it becomes more difficult to identify all the tables actually accessed by the optimizer. You can use
STATISTICS IO
to get this information, irrespective of query complexity.
To turn
STATISTICS IO
on, navigate to Query
➤
Query Options
➤
Advanced
➤
Set Statistics IO in Management
Studio. You may also get this information programmatically as follows:
SET STATISTICS IO ON;
GO
SELECT soh.AccountNumber,
sod.LineTotal,
sod.OrderQty,
sod.UnitPrice,
p.Name
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product p
ON sod.ProductID = p.ProductID
WHERE sod.SalesOrderID = 71856;
GO
SET STATISTICS IO OFF;
GO
If you run this query and look at the execution plan, it consists of three clustered index seeks with two loop
joins. If you remove the
WHERE
clause and run the query again, you get a set of scans and some hash joins. That's an
interesting fact—but you don't know how it affects the query I/O usage! You can use
SET STATISTICS IO
as shown
previously to compare the cost of the query (in terms of logical reads) between the two processing strategies used by
the optimizer.
You get following
STATISTICS IO
output when the query uses the hash join:
(121317 row(s) affected)
Table 'Workfile'. Scan count 0, logical reads 0...
Table 'Worktable'. Scan count 0, logical reads 0...
Table 'SalesOrderDetail'. Scan count 1, logical reads 1246...
Table 'SalesOrderHeader'. Scan count 1, logical reads 689...
Table 'Product'. Scan count 1, logical reads 6...
(1 row(s) affected)
