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:
117559
: How to Correlate Spid, Kpid, and Thread Instance
Sysprocesses.physical_io indicates synchronous reads and writes done by a
specific spid. However, after SQL Server was enhanced with read-ahead,
lazywriting and asynchronous checkpointing (which use separate threads), it
was no longer easy to decide which user spid should be charged for IO done
by these common service threads.
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.