SQL Server and threads

Question

Does a JOIN query under SQL Server always spawn an new thread? In general how does SQL Server control thread spawning?

Thanks, -peter

Answer 1

SQL Server 2005 and later can execute a single queries using multiple threads when it determines that the query can benefit. This is based on the time required to gather necessary information, pre-filtering indexes, and then joining the multiple-thread data together. It's not automatic based on a join, it happens based on much more complicated criteria.

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

During query optimization, SQL Server looks for queries or index operations that might benefit from parallel execution. For these queries, SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. The exchange operator includes the Distribute Streams, Repartition Streams, and Gather Streams logical operators as subtypes, one or more of which can appear in the Showplan output of a query plan for a parallel query.

Answer 2

Certain operations in a query can be parallelized, in particular scans over wide ranges. The query optimizer determines what operations could benefit from parallelism and creates a parallel plan, see Parallel Query Processing. also certain DDL operations can be executed in parallel, see Parallel Index Operations.

But one thing is pretty sure: a JOIN operator would not introduce parallelism. The rowset joined in by this operator may introduce parallelism, that is true, but not the JOIN.