INF: Differences in STATISTICS IO, SQL Profiler and Sysprocesses IO Counters

Article translations Article translations
Article ID: 314648 - View products that this article applies to.
This article was previously published under Q314648
Expand all | Collapse all

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.

Properties

Article ID: 314648 - Last Review: October 17, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbinfo KB314648

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com