It's parallelism. It's something that SQL decides to do, not the developer.
Not all of the threads will likely be running and there can be more than the maxdop set or the number of cores. Maxdop sets the max parallelism per operator, so if the maxdop (or no of cores) is 8, you can have a couple of operators not running (each with 8 threads) and then one that is running (also with 8 threads)
Ariadne (1/9/2009)
(I scaled parallelism back to 6 from 0 and that helped the CPUs overall, but did not cut down on CXPacket waits).
Wrong way. 0 means that queries can parallel across all cores. How many processing cores do you have? OLTP or datawarehouse type app?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability