Why doesn't query parellelism use all of the available CPUs?

Question

I have a complex query that, according to the execution plan, uses parallelism, which is what I would expect. But when the query actually runs, it doesn't appear to use all of the available CPUs to their full potential. For example, when I watch Performance Monitor when I run the query, it appears that only one of the CPUs is really being used heavily, and all the rest are not all that busy. Our server has 8 CPUS and 6.5GB RAM. Is there any way to force SQL Server to use all of the available CPUs for running parallel queries in order to fully take advantage of all the CPU power that is available, further speeding up the execution of the query?

Answer

First, let me answer your question, then I will discuss a little about what you are experiencing. No, you can't force SQL Server to use more CPUs that it wants to use when executing a parallel query. On the other hand, you can prevent SQL Server from using all of the available CPUs, but the most you can do is to tell SQL Server to use parallelism, and that is it. Only SQL Server can decide how to take advantage of the available CPUs.

Now, let's talk a little bit about how parallelism works and why SQL Server might not take full advantage of all the available CPUs in your server.

By default, if your server has two or more CPUs, and assuming parallelism has not been turned off, and the number of available CPUs for use by SQL Server has not been restricted to a single CPU, and if the Query Optimizer thinks it will take less than 5 seconds (the default value, you can change this if you want) to execute, then the Query Optimizer will consider using parallelism for executing a query.

By parallelism, what we mean is that SQL Server will consider splitting the query into two or more execution threads and running them on multiple CPUs in your server. In many cases, this can speed up the execution of the query, but not in all cases. The use of parallelism has its own overhead, and the time savings of using parallelism must exceed the overhead incurred before the Query Optimizer will use it. This makes sense.

When the Query Optimizer evaluates a query for parallelism, it must consider many factors, such as the current load on the server, the nature of the query, I/O requirements, and so on. This is a very complex decision-making process as you might expect. Once it is done with this process, it decides on the optimum query plan, which may use two or more CPUs. We can't control how many will be used, only the Query Optimizer can do this.

In regards to your query and the use of the CPUs that you saw with Performance Monitor. There are several potential explanations for the behavior you saw. Some of them include:

• The Query Optimizer decided that using more CPUs to execute the query would not provide any performance benefit. For example, the bottleneck to performance for this query may be disk I/O, not CPU, and using more threads may not have helped performance.
  
• Just because there was only one very busy CPU does not necessarily mean that other CPUs were not also executing part of the query. For example, it is possible that most of CPUs were involved executing the query, but that only one of the execution threads was overly busy, while the other execution threads were running, but just at a much lower level based on the amount of work allocated to them.
  
• Another thing that can affect your observations with Performance Monitor is that an executing SQL Server thread can jump from CPU to CPU as needed in order to optimize performance. Because of this, watching CPU activity in order to evaluate the performance of parallelism is not always very helpful.
  
• Perhaps the Query Optimizer made a mistake. This doesn't happen often, but it does happen.

So, unfortunately, there is no way to force SQL Server to use all of the CPU power that is available.