Article ID: 314648 - View products that this article applies to.
This article was previously published under Q314648
There are several different places in which SQL Server reports different input/output (IO) related 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:
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.
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.
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:
Contact us for more help
Connect with Answer Desk for expert help.