SUMMARY
There are several different places in which SQL Server
reports different input/output (IO) related information:
- The physical_io column of the sysprocesses system table.
- Informational messages returned in a batch when the
STATISTICS IO option is enabled.
- The Reads or Writes column of relevant events in a SQL
Profiler trace.
Each of these counters report slightly different information.
Depending on what you are want to measure, a given counter may be more
appropriate to use than another. This article describes what each counter
measures so that you can use the information appropriately.
MORE INFORMATION
Each SQL Server connection has an associated process status
structure (PSS) that maintains connection-specific state information. Each
unique server process ID (SPID) in the
sysprocesses system table represents a different PSS, and the information in
the
sysprocesses virtual table is a "view" into this status information.
SQL Server distinguishes between physical IO (that is, the number of times that
SQL Server actually makes a Win32 system call to read or write a page) and a
logical IO (any time SQL Server requests access to a page). The higher-level
query processor routines in SQL Server use a mechanism to request logical
access to a page. This routine searches to see if the page is already in cache,
and if so, provides access to that buffer; if the page is not in cache, the
routine is responsible for requesting a physical IO and waiting on the IO to
complete before providing access.
For each connection, SQL Server
maintains counters to track the number of physical reads and physical writes.
Any time SQL Server requests a physical read or write as part of servicing work
for your connection, the appropriate physical IO counter within the PSS
increments. The
physical_io column of
sysprocesses reports the sum of these two physical IO counters.
If
STATISTICS IO is enabled for a connection, SQL Server allocates an array during
query execution to track IO information on a per-table basis. As SQL Server
processes the query, it records each logical request for a page in the
appropriate table's entry in this array, along with whether that logical IO
request resulted in a physical IO. SQL Server returns the information, at the
end of the query, in error message 3615:
Table
'authors'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads
0.
Table 'titles'. Scan count 1, logical reads 2, physical reads 2,
read-ahead reads 0.
SQL Profiler reports the number of logical reads
and physical writes performed in processing the statement or batch. Note that
the Microsoft SQL Server 7.0 documentation regarding these counters contains
contradictory information.
In comparing the numbers reported by SQL
Profiler with those from STATISTICS IO, it is important to note that logical
and physical IOs may be incurred doing work that is related to your query or
batch but not specific to the query itself. For example:
- During compilation and optimization, there are reads of
system tables to obtain information about the table's schema.
- There may be access to system tables to check permissions
on objects at execution time.
- If you execute a stored procedure and SQL Server does not
find it in the cache, SQL Server may perform reads to load the text of the
procedure from the syscomments system table.
- If your query causes an error, IOs take place as SQL Server
reads the error text from the sysmessages system table.
- There may be IOs on worktables not reported in STATISTICS
IO, and so forth.
As a result, in general, the logical reads reported by SQL
Profiler do not match up with the sum of those reported by STATISTICS IO.
However, the number reported for reads in SQL Profiler should always be equal
to or greater than the STATISTICS IO values.