Parallel Query Processing

有時我們會在sysprocesses中看到很多duplicate SPID，但是有著不同的KPID。

spid   kpid------ ------55     376055     368455     595655     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