Earlier I had posted article answering question.
“When SQL Server executes any query on multiple processors, do all processors process equal numbers of rows?”
Read the answer over SQL SERVER – Parallelism – Row per Processor – Row per Thread.
In the same article, I had asked back to readers as well.
“If you look carefully in the Properties window or XML Plan, there is “Thread 0″. What does this “Thread 0” indicate?”
Here is the answer of the question, many thanks to all of you and special mention to Marko Parkkola, who has answered first and the answer is very detailed.
Thread 0 is used in synchronization of the other threads. It gather all the worker threads and their output data. It does not do actual data fetching so it has ActualRows always set to zero. A quote from MSDN blog:
“The reason is “MAXDOP is always specified per operator in the execution plan and not per execution plan”.
1 thread is used for parallel gather streams operator to collect and converge the parallel thread output from the Nested Loop join operator. This thread is basically a synchronizing thread (also referred as Thread 0 IN xml plan) which is used when there is parallelism in the execution plan of the query.”
The thread 0 is shown in all operators of the execution plan and is called a synchronizing thread. The thread’s function is to gather all the stream from all parallel threads and bring the result to the client.
Thread 0 is the coordinator thread which is not responsible to process any rows. It collects all the rows processed by other threads and show as query result.
Here thread1 and 2 are not processing equal number of rows. i think ,thread 1 might have some other load processing on it.
Thread 0 is the forked process and probably acts as a synchronizer / controller.
I really appreciate the dedication to community and knowledge shown by experts.
Reference: Pinal Dave (https://blog.sqlauthority.com)