Determine the MAXDOP (Maximum degree of Parallelism) on machines with t_SQL:
select * from sys.configuration where name = ‘max degree of parallelism’
NB: DBCC check DB or check indexes will not run in parallel except in Enterprise regardless of the MAXDOP setting of the MS SQL server
parallel tasks should be concurrrent and independent of each other
Sample from Paul White for a query like
vs the query plan when it goes into parallel execution
Notice the orange arrows indicating that that task is running in parallel
Ideal all of the stream aggregate processes will reach the gather stream step at the same time however the task (here an index scan)
- might require different amounts of work on different workers
- there might be different loads on different workers – contention
- if the task involves activities such as joins, the efficiency of the task might be influenced by the content of the data – some joins might be faster/more efficient and other works might have more difficult activities and finish slower.
To partly deal with this the storage engine has a “Parallel Page Supplier” that can change the delivery of data to different workers – more to proformant works, less to slower running workers. Generally responsible for ‘cooperative’ management of workers reading one data structure.
If the tasks don’t finish at the same, a CXPacket Wait will occur when the operation is waiting on the delivery of rows. Execsync waits can occur but it is NOT due to row delivery. Some LATCH_xx waits dealing with synchronization can also occur.
Schedulers Workers and Tasks
When SQL runs a parallel query plan as multiple serial query plans, an Exchange puts it together, collecting from the different workers. Three types: gather streams, distribute streams and repartition streams. Can have several exchanges in a complex plan – because several parts of the plan might go from parallel to serial regions or back.