INF: How to Correlate Spid, Kpid, and Thread Instance

  - Microsoft SQL Server, version 4.2x and 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.