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 (https://blog.sqlauthority.com)