Answer: Yep, sometimes it can.
I used to think that higher degrees of parallelism followed a law of diminishing returns– you could add more threads, but the benefits would taper off. But it’s a bit more complicated than that. Microsoft’s recommendation to be careful when setting maxdop to values over 8 is a warning worth heeding.
Lowering maxdop can cut CPU without sacrificing duration
We tend to think that reducing the number of threads available MUST make a query slower– but that we sometimes need to do it so other queries can run concurrently.
It’s great to support concurrency, but lower DOP doesn’t necessarily mean a longer runtime, even if it does lower CPU consumption. Here’s a simple example of an OLTP query run with two different DOP settings. CPU time and duration were measured using “SET STATISTICS TIME ON”. For both runs all data was in memory (no physical reads or read aheads).
Duration in both cases was around 700 milliseconds. The lower DOP didn’t make execution time longer. It changed the way SQL Server ran the query, but it cut overall CPU usage while keeping the runtime about the same. This was great in this case, because the query in question needed to run frequently on an OLTP system.
Higher maxdop can slow down large queries
In the case of large queries, higher DOP can slow down query execution. The impacts here vary a lot by processor, memory type and amount, and whether or not your SQL Server is virtualized— not to mention based on the execution plan of the query in question.
Here’s a totally different query tested on totally different hardware. In this case the query reads in hundreds of gigabytes of data, but as in the previous example all tests were run against a “warm” cache and were not doing physical reads or read ahead reads. The server used in this test had 8 physical cores per NUMA node.
Changing maxdop changes query plans
When you tune maxdop, it’s worth watching the execution plans for the top queries on your server. I’ve seen changing the amount of threads available for a query make the optimizer change its mind about how to run a query immediately– and been able to reproduce it switching the plan back as soon as I hint a different DOP.
Since impacts can be complicated I recommend changing maxdop rarely and monitoring your plan cache and wait stats for at least a week or two after the change.
Parallelism is a good thing
Don’t get me wrong– I ain’t saying parallelism is bad for SQL Server. Multiple threads can make many queries faster!
One of the gotchas with SQL Server is that the default value of “0″ for the “Max Degree of Parallelism” setting can lead to poor performance– because it lets SQL Server use all your processors (unless you’ve got more than 64). Fewer threads can not only reduce CPU usage, but may also be faster.
So check your maxdop settings, and keep reading to learn more about CXPACKET waits.