SQL SERVER – Parallelism – Row per Processor – Row per Thread

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.

SQL SERVER - Parallelism - Row per Processor - Row per Thread paral1

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.

SQL SERVER - Parallelism - Row per Processor - Row per Thread paral2

SQL SERVER - Parallelism - Row per Processor - Row per Thread paral3

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.

SQL SERVER - Parallelism - Row per Processor - Row per Thread paral4

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)

,
Previous Post
SQL SERVER – Introduction to Best Practices Analyzer – Quick Tutorial
Next Post
SQL SERVER – View XML Query Plans in SSMS as Graphical Execution Plan

Related Posts

5 Comments. Leave new

  • Marko Parkkola
    July 2, 2010 9:25 am

    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.”

    Reply
  • Feodor Georgiev
    July 2, 2010 10:15 am

    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.

    Reply
  • 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

    Reply
  • Thread 0 is the forked process and probably acts as a synchronizer / controller

    Reply
  • Feodor Georgiev
    July 16, 2010 5:29 pm

    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.

    Reply

Leave a Reply

Menu