When you debug certain types of problems, it is periodically necessary tocorrelate a particular spid number with its corresponding kpid and/orthread instance number. This article explains how to do this.
"Spid" is the SQL Server process ID number and is assigned by SQL Server toeach new connection. It starts with one and is globally unique. It is seenas 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 isthe thread ID number, also known as "ID thread," and is assigned by WindowsNT when the thread is created. The thread ID number is a system-wideidentifier that uniquely identifies the thread and is available by callingthe Win32 API GetCurrentThreadID().
Kpid is visible by querying the kpid column of master..sysprocesses. It isonly filled in for spid numbers four and higher. The Windows NT PerformanceMonitor (Perfmon) object "ID Thread," and the Windows NT Resource KitUtility PSTAT display thread ID numbers (note PSTATs are in hexadecimal).
The "Thread instance number" is a monotonically incrementing numberstarting at zero, that identifies the threads within a process. Threadinstance numbers are used by the NTSD and WINDBG debuggers' "~" command,the Windows NT Perfmon object "thread," and Windows NT Resource Kit UtilityPviewer.
When debugging a variety of problems, the need to correlate spid, kpid, andthread 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 existingsysprocesses.physical_io counter is only accumulated for actual physicalI/O, not logical I/O. During some types of long-running queries, most ofthe I/O can be logical because of the efficient SQL cache. Also, under someconditions, the sysprocesses.cpu counter may not accurately reflectresource consumption, necessitating external verification.