Degree of parallelism and scheduler usage

Question

Is it ever possible for a SQL Server query running with an effective degree of parallelism x to have parallel workers assigned to more than x distinct schedulers? Even if the execution plan has many parallel zones?

Answer 1

Up to SQL Server 2000 yes; in SQL Server 2005 and later, no.

According to Craig Freedman's Parallel Query Execution Presentation, up to SQL Server 2000, any parallel worker thread could run on any CPU. That means there may be more active threads than DOP, so reducing DOP might not actually reduce CPU usage. To be clear, a single parallel query might indeed use more than x distinct schedulers if it had more than one parallel branch/zone in the execution plan.

The behaviour was changed from SQL Server 2005 onward so that the number of schedulers per query is limited to DOP. That means a single parallel query never uses more than DOP CPUs, and therefore reducing DOP does reduce CPU usage. This applies regardless of the number of parallel zones (branches) in the execution plan.

My understanding is that the pre-2005 behaviour was an implementation bug/oversight.

---

Note: The additional parallel worker threads are limited to DOP schedulers; e.g. a parallel query that runs at DOP 4 on an instance with 8 schedulers available would use 4 schedulers for the parallel workers. The query as a whole could use 5 schedulers: 1 provided by the session plus the 4 for the additional parallel workers.