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)
SELECT o.SalesOrderID, COUNT(*) AS Order_Count
FROM Sales.SalesOrderDetail o
FROM Sales.SalesOrderHeader I
GROUP BY o.SalesOrderID
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
(5 row(s) affected)
I have included “GO 100” to execute the query for 100 times.