Does SPID unique in sys.sysprocesses?

Sys.sysprocesses contains connection information for each connection made to the SQL Server. As you see it, it looks like SPID is unique. But actual it is not, because of KPID. KPID is Windows thread id. Each SQL Server task is assigned a Windows thread. SQL Server task is the unit of execution for SQL Server. The queries submitted to SQL Server may have parallel tasks like parallelism operator. In this case, SPID has several KPIDs, which duplicate SPID in the sys.sysprocesses view.

See below query. (created only to demonstrate parallelism)

use adventureworks

GO

SELECT @@SPID

GO

SELECT o.SalesOrderID, COUNT(*) AS Order_Count

FROM Sales.SalesOrderDetail o

WHERE EXISTS(

                   SELECT *

                   FROM Sales.SalesOrderHeader I

                   WHERE o.SalesOrderID>I.SalesOrderID                     

                   )

GROUP BY o.SalesOrderID

GO 50

While executing this query in one query window, open a another query window and execute the below statement for several time. Remember to change the SPID of the query to reflect the SPID of the first query windows where you’ve the parallel query.

SELECT spid,kpid from sys.sysprocesses WHERE SPID=55

GO 100

spid   kpid

------ ------

55     3760

55     3684

55     5956

55     3680

55     3260

(5 row(s) affected)

 I have included “GO 100” to execute the query for 100 times.