One of my repeat clients of Comprehensive Database Performance Health Check recently asked me if a query goes to parallel, do they use all the threads. The answer is not necessary. We can have parallelism and thread with no work. Let me show a demonstration.

Let us run the following query and make sure that the execution plan is enabled.

When we run the above query and check the execution plan, it will show many parallelism operators. Let us click any one of the parallelism operators first. Once the operator is highlighted, click on its properties of it.

When you check the properties, you will see the table Actual Number of Rows for All Execution. You can expand it and will be able to see all the threads. There are few threads that are empty or have zero value.

This means, out of 16 threads available on my system, not all the threads are used for the doing operation of the operator. If you click through the execution plan, you will see different threads doing different tasks and not the same threads continuing for that query.

I hope this helps you to understand how parallelism works a little bit more. It is totally possible that threads are doing no work.

Technology Online

I hope you find these Learning paths helpful. If you have a Pluralsight subscription, you can watch it for free. If you do not have a Pluralsight subscription, you can still watch the course for FREE by signing up for a trial account. Please note that you do not need any credit card.

Reference: Pinal Dave (http://blog.SQLAuthority.com)