Here is a question I received via email:
“When SQL Server executes any query on multiple processors, do all processors process equal numbers of rows?”
I find this one very interesting. I quickly wrote down a query which can run on multiple CPU in my machine. My laptop has a Core 2 Duo processor and has two CPUs. When I ran the query, I found out from the execution plan that there is a parallelism operator, which runs my query in both CPUs.
I pressed F4 to see the Properties of the execution plan. You can open the Properties window by clicking on Tool bar >> View >> Properties Window. Check the Version property of Actual Number of Rows and expand it. You will see details for Thread 1 and Thread 2, including the numbers of rows they processed individually. You can see that the numbers of rows are not the same.
You can also check the same thing in XML Plan. Right click on Execution Plan and click on “Show Execution Plan XML” to see the XML Plan. Here you can check the node RunTimeInformation and find details on numbers of rows that were executed.
Now I have a question for all of you:
If you look carefully in the Properties window or XML Plan, there is “Thread 0”. What does this “Thread 0” indicate?
Please leave your comments, and I will post the correct answer in this blog with due credit.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
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 resposible to process any rows. It collects all the rows processed by other threads and show as query result.
Here thered 1 and 2 are not processing equal nuber of rows. i think ,thread 1 might have some other load processing on it.
Thanks
Manoj kumar
Thread 0 is the forked process and probably acts as a synchronizer / controller
If you would like to know which queries are currently executing with parallelism, here you can find a query which will give you this information: . The query shreds the cached plans.