At a customer of ours, the sql server is configured to 1 for degree of parallelism. The server has 8 cpu's, so what would be reason to limit this degree to 1?


SQL Server will already put multiple queries to multiple CPUs (if licensed). On larger queries it can break the single query across multiple threads (and potentially CPUs) by enabling parallelism.

You should set the degree of parallelism to match the general size of large queries. If you're constantly hitting the DB with a 72 join query, set it up to as many CPUs as the server has (or is licensed for). If you constantly hitting the server with small queries, or you don't want larger queries taking over all the CPUs, then set this for a more conservative number (like 1).

These are very generic guidelines, more info from MS on Parallel Query Processing, and the Degree of Parallelism setting.


The scenario I see often is a query causing a deadlock with itself when executed with parallelism; that's usually a sign of bad indexing or a poorly written update/delete, but some people go the quick and dirty route and turn parallelism off to avoid the deadlocks.


Here's how I would answer, purely based on my experience and limited knowledge:

SQL Server seems to make a decision on whether to use parallelism in a query plan mainly on the basis of whether or not this will help a single execution of that query to return faster. Most of the time, this assessment is fairly accurate; occasionally, the optimizer may make a choice that doesn't turn out so well. But in either case, it doesn't take into account what else is going on with that server. In particular, if you are running a server that is handling a large number of batch requests per second, and some significant portion of those batches are being parallelized, you can encounter thread starvation. That is, the schedulers are all busy processing the parallelized requests or waiting on them and nothing can get processed. This can manifest itself as unresponsiveness from SQL Server. Note that you may not necessarily see 100% CPU utilization. It's not CPU that you are out of, it's available schedulers. Typically, you will a lot of CX_PACKET and possibly THREADPOOL waits, and an average runnable task count across schedulers that's above 1.

In some shops, the workload is so complex that a strategic decision is made to completely eliminate parallelism to prevent this problem altogether. This can work quite well in some situations, as far as I know, although I have spoken to Microsoft engineers who thought it was a misguided and essentially bad idea. In such a scenario, you can't speed up a query with parallelism, so execution time is generally proportional to CPU utilization. This can be an acceptable tradeoff, especially if most of your batch requests are small.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.