INF: Information on CPU and Physical_io Columns of Sysprocesses

• Microsoft SQL Server, versions 6.0 and 6.5

SUMMARY

The CPU and physical_io columns of the sysprocesses system table are often queried to inspect the status of running processes. SQL Enterprise Manager queries these columns when you click Current Activity on the Server menu, and then click the Detail Activity tab. If an operation is taking a long time to complete, some questions may arise about the meaning of these columns and how the information is calculated. This article is intended to provide answers to these questions.

MORE INFORMATION

Each database connection forms a row in sysprocesses, sometimes called a Server Process ID, or spid. In SQL Server terminology, each connection is also called a "process," but this does not imply a separate process context in the usual sense. In SQL Server 6.0 and 6.5, each process is roughly analogous to and serviced by a separate operating system thread. Each database connection also consists of server data structures that keep track of process status, transaction state, locks held, and so on. One of these structures is called the Process Slot Structure (PSS), of which there is one per connection. The server scans the list of PSSs to materialize the sysprocesses virtual table. The CPU and physical_io columns from sysprocesses are derived from the equivalent values in each PSS.

Sysprocesses.cpu indicates the CPU milliseconds of user-mode time the process has consumed. It is derived from the Win32 API GetThreadTimes(). See the Win32 documentation for more details. Sysprocesses.cpu is updated at the start of each language or remote procedure call (RPC) event, when doing cursor asynchronous keyset generation, or when sending a data buffer or done token to the client. See the SQL Server documentation under "Open Data Services" for details on language and RPC events.

Therefore, sysprocesses.cpu is accurate but only updated at certain points. For lengthy operations that do not return any data to the client, it may not be updated for long intervals of time. Some of these operations may include DBCC CHECKDB, CREATE INDEX, database recovery, long-running joins, and so on. From a practical standpoint, it may often appear to be updated only at the end of each command.

If you need to determine if a spid is consuming CPU resources under conditions where sysprocesses.cpu is not incrementing, you can do this by correlating the spid to a particular operating system thread and inspecting it with Windows NT Performance Monitor. For more information, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q117559
   TITLE     : How to Correlate Spid, Kpid, and Thread Instance

I/O done by read-ahead, lazywrite, checkpoint, and some other operations are not counted in the per-spid physical_io counter. Almost all non-read- ahead physical reads are counted because each user spid does its own physical read, but almost no physical writes are counted because user spids rarely do synchronous writes.

During normal processing, synchronous writes are only done to flush the new page after a split. Logwrites are not included in the per-spid counters because a logwrite may contain data from many spids. All IO reported by the per-spid counters is 2-KB IO.

From a practical standpoint, sysprocesses.physical_io will usually be updated more frequently than sysprocesses.cpu. This is because each synchronous read or write updates it. However for operations where the bulk of the spid's I/O is serviced by lazywriter, read ahead, or checkpoint threads, the I/O counter will not appear to increment.

Also, if the read/write operations are serviced out of SQL Server's cache and no physical I/O is done, sysprocesses.physical_io will not increment.