Saturday, September 18, 2010

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.

No comments:

Post a Comment

How to fix cardinality estimation anomalies [Video]

Use the link mentioned below to watch the presentation that I delivered for PASS DBA Virtual Chapter about Filtered Statistics.  http:...