INF: How to Correlate Spid, Kpid, and Thread Instance
ID: Q117559
|
The information in this article applies to:
-
Microsoft SQL Server versions 4.2x, 6.0
SUMMARY
When you debug certain types of problems, it is periodically necessary to
correlate a particular spid number with its corresponding kpid and/or
thread instance number. This article explains how to do this.
MORE INFORMATION
Definitions:
"Spid" is the SQL Server process ID number and is assigned by SQL Server to
each new connection. It starts with one and is globally unique. It is seen
as the spid column of master..sysprocesses, as the spid column from sp_who,
or the "Proc ID" column when you select Manage/System in SQL Administrator.
"Kpid" is the kernel-process ID. Under SQL Server for Windows NT this is
the thread ID number, also known as "ID thread," and is assigned by Windows
NT when the thread is created. The thread ID number is a system-wide
identifier that uniquely identifies the thread and is available by calling
the Win32 API GetCurrentThreadID().
Kpid is visible by querying the kpid column of master..sysprocesses. It is
only filled in for spid numbers four and higher. The Windows NT Performance
Monitor (Perfmon) object "ID Thread," and the Windows NT Resource Kit
Utility PSTAT display thread ID numbers (note PSTATs are in hexadecimal).
The "Thread instance number" is a monotonically incrementing number
starting at zero, that identifies the threads within a process. Thread
instance numbers are used by the NTSD and WINDBG debuggers' "~" command,
the Windows NT Perfmon object "thread," and Windows NT Resource Kit Utility
Pviewer.
When debugging a variety of problems, the need to correlate spid, kpid, and
thread instance number can arise. For example:
When you are monitoring SQL Server with sp_who, and note a
particular spid's query is taking inordinately long to complete, you
want independent verification of the CPU resources it is consuming.
Repeat the approximate equivalent of sp_who, by issuing this
query:
select spid, kpid, status, hostname, dbid, cmd
from master..sysprocesses
Monitor the SQL threads' CPU consumption by selecting the "thread"
object, and all of the "sqlservr" instances in Perfmon. This will
display %Processor Time by thread instance number, which is different
from spid number.
To correlate a Perfmon thread instance to a spid number, start
another copy of Perfmon, and select View/Report. Then Edit/Add the
object "thread," and the counter "ID Thread."
Select all of the "sqlservr" instances to add. This will appear as
a tabular report for which a thread ID number is listed below the
corresponding thread instance number. The thread ID number is the key
that ties the a thread instance number to a corresponding spid number
obtained previously.
A practical reason for wanting to do this is because the existing
sysprocesses.physical_io counter is only accumulated for actual physical
I/O, not logical I/O. During some types of long-running queries, most of
the I/O can be logical because of the efficient SQL cache. Also, under some
conditions, the sysprocesses.cpu counter may not accurately reflect
resource consumption, necessitating external verification.
Additional query words:
sql6 windows nt perfmon reskit
Keywords : kbtool SSrvAdmin SSrvWinNT
Version : 4.2 6.0
Platform : WINDOWS
Issue type :
|