Question: What is the difference between spid, kpid and ecid in sysprocesses?
Answer: When I received this question, I actually went and checked SQLAuthority.com blog as I thought I have blogged about it, but the reality was that I had not blogged about it before. So here it goes.
First, let us understand where we can find about three columns. We can find above three columns in sys.sysprocesses table. You can see that by running the following script:
SELECT spid,kpid,ecid FROM sys.sysprocesses
Now let us understand what each of the columns means.
Every process in SQL Server is running under a server process. They are referred to as session IDs. Each of these session processes has a unique ID known as SPID.
SPIDs are in the context of SQL Server. The same processes run in Operating System as a Kernel Process. Each Kernal Process has its own ID which is known as KPID.
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 known as ECID.
Well, that’s it. I hope now it is clear now what each of the columns means.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Perhaps worthwhile mentioning that ECID is an acronym for Execution Context ID.
Fine, but what about kpid = 0 for user processes ?
On Sybase we had a kpid value for all connected user, how can I replace it on MSSQL ?
In SQL, KPID is only for running query not connection.
How can I relation ecid or spid with Object_ID ?