SQL Server Performance Tuning: Pt. 1 - Page 4

Statistics IO

In Query Analyzer you can turn on the display of statistics by setting the session option:

Set Statistics IO ON

Or by changing the current connection properties under the Query menu:

SQL 7.0 Connection Options
(Click image for full size)

SQL 2000 Connection Options
(Click image for full size)

Here is sample output for Statistics IO: [Editor's note: lines in the following output were reformatted for better display on this Web page.]

Table 'dbi'. 
  Scan count 1, logical reads 3, 
  physical reads 0, read-ahead reads 0.
Table 'matter_resolution'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'dbi_bm'. 
  Scan count 1, logical reads 5, 
  physical reads 2, read-ahead reads 0.
Table 'dbi'. 
  Scan count 1, logical reads 3, 
  physical reads 1, read-ahead reads 0.
Table 'initial_matter'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'processing_summary'. 
  Scan count 5, logical reads 2892, 
  physical reads 0, read-ahead reads 209.
Table 'amount_detail'. 
  Scan count 2, logical reads 2091, 
  physical reads 0, read-ahead reads 2096.
Table 'system_user'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'case_type_lu'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'processing_action_type_lu'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'dbi_bm_charge_event'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'event_by_dbi_bm'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.

Pages accessed is the important thing here. The output shows that the two tables processing_summary and amount_detail are the worst performing parts of the query with logical read counts in the thousands whereas the other tables only call for a few pages each.

Statistics IO is good for a quick read, but is very difficult to use for long stored procedures because the output is not delimited. There is no quick way to tell which SQL statement these statistics refer to.

Some Definitions:

Page: A unit of storage in SQL Server. In Version 7 a page is 8K and stores data, indexes, or code. Whenever SQL Server is looking through a table or an index, it pulls data from memory or disk a page at a time.

Data Page: As used in this article, a page containing table data as opposed to index data. Index entries point to table data pages.

Query Plan: The exact steps SQL Server uses to access or update data.

Optimizer: The component in SQL Server which generates a query plan.

Table Scan: Searching for data by checking each row in the table one by one--very slow for medium and larger data sets, quick for small datasets.