notes on parallel processes in MS SQL

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.

Execution Context

Schedulers Workers and Tasks

Exchanges

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.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.