Quick one today:

Very often, when dealing with multi-threaded processes and in other cases dealing with deadlocks we run into some interesting artifacts. In this post, we’ll see the meaning of some identifiers (id’s).

Specifically

• spid
• ecid
• kpid

Spid

Every process in Sql Server is carried out under a servers process; Sometimes these are also referred to as session ID or connection ID. Essentially, they have all the memory structures necessary to carry out any activity with in Sql Server. Each of these processes have unique ID, called SPID. You could view all the active SPIDs using

• master..sysprocesses
• EXEC sp_who2
• SELECT @@SPID
• Activity Monitor
• etc

Ecid

If a process is multi-threaded, all the sub-thread have the same parent Server Process ID (SPID). Each sub-thread has its own unique identifier called ecid. It defines the execution context of each subthread operating on behalf of the parent process. Sometimes in intra-query parallelism deadlocks, these sub-threads sometimes run into deadlocks.

Kpid

SPIDs are in the context of Sql Server. The same processes, under the context of operating system kernel have their own unique identifier, its called KPID a.k.a. Kernel Process ID. All activities for the SPID are carried out using the memory structures, scope, etc available to carry out operations within Windows Operating System. Using Win32 API we could gather information about these threads at OS level. From within Sql Server we could query Kpid using master..sysprocesses

When you open Task Manager, you can view this information under PID column.

Hope this helps,
_Sqltimes