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

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.

Marko Parkkola

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

Feodor Georgiev

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.

Manoj Kumar

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.

Thanks
Manoj kumar

Mateus

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

About these ads

5 thoughts on “SQL SERVER – Parallelism – Row per Processor – Row per Thread – Thread 0

  1. Thank you very much but all I did was little googling, read the MSDN blog and drew some threads together :)

    But I shamelessly take the opportunity here to ask if someone can help me with a problem I’m having with our old SQL Server 2000 (standard) running on Windows 2003 SP2. It has 16 CPUs (Intel Xeon), 24 gigabytes of memory (PAE enabled) and 4 harddrives (RAID0+1 I think, not sure about this).

    From time to time it seems that server’s procedure cache fills up. After that server starts to really churn hard drives causing major I/O issues, heavy latency and some connections break down to Sql Server. When this happens we conjure DBCC FREEPROCCACHE and things goes back to normal.

    Now we have two such system elsewhere with much less hardware. One has 2 CPUs and 4 gigs of memory, the other 2 CPUs and 2 gigs of memory. Both are running W2003 and Sql Server 2000. They are running just fine but they have much lower utilisation than the big one.

    So the question is, is there anything I can do to alleviate the problem besides going through the actual SQL queries? I can’t change the queries but I can administer the server otherwise.

    Luckily I have to support this server only to this year’s end and then I can replace it with much heavier system with newer Windows and Sql Server.

    Thanks for advance for any suggestions.

    • IIRC there’s plenty of plans for ad-hoc queries and stored procs. I can’t remember the ratio and I’m on vacation now for couple of weeks so I can’t check it.

      But anyway, I can’t do nothing to the queries. The system is written by one of our subcontractor and they charge plenty of money for any changes to the system.

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

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #037 | 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