Article ID: 117559
Article Last Modified on 6/14/2004
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 4.21a Standard Edition
This article was previously published under Q117559
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: kbinfo KB117559
