Parallel Query Processing

有时我们会在sysprocesses中看到很多duplicate SPID，但是有着不同的KPID。

spid   kpid

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

55     3760

55     3684

55     5956

55     3680

55     3260

这篇文章解释了这个现象：

http://www.sqlservertorque.net/2010/09/does-spid-unique-in-syssysprocesses.html

其中这句话有点意思：KPID is Windows thread id. Each SQL Server task is assigned a Windows thread.

相关的Parallel Query Processing官文：

http://msdn.microsoft.com/en-us/library/ms178065%28v=sql.105%29.aspx

SQL Server provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). Because SQL Server can perform a query or index operation in parallel by using several operating system threads, the operation can be completed quickly and efficiently.

The actual number of threads used by a parallel query is determined at query plan execution initialization and is determined by the complexity of the plan and the degree of parallelism.The degree of parallelism value is set at the server level and can be modified by using the sp_configure system stored procedure. You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.

这里有另外一篇文章解释MAXDOP：

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/01/05/why-do-i-see-more-threads-per-spid-in-sysprocesses-than-maxdop.aspx