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.

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 (http://blog.SQLAuthority.com)

About these ads

9 thoughts on “SQL SERVER – Parallelism – Row per Processor – Row per Thread

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

    http://blogs.msdn.com/b/sqlserverfaq/archive/2009/01/05/why-do-i-see-more-threads-per-spid-in-sysprocesses-than-maxdop.aspx

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

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

  4. Pingback: SQL SERVER – View XML Query Plans in SSMS as Graphical Execution Plan Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – Parallelism – Row per Processor – Row per Thread – Thread 0 Journey to SQL Authority with Pinal Dave

  6. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s