Blitz Result: Parallelism Confusion

Parallelism is a madman, dreaming of a madman, dreaming of a madman, dreaming of queso con carne.

Just kidding!

It’s a feature, not a bug, and it’s pretty nifty when you configure your server properly to handle it for your workload. If you’re here, you’re probably asking yourself: “How?”

We’re here to show you, and also teach you if you’re interested beyond changing the settings.

So, first up is Brent waxing in (up to) full 1080p HD video about CXPACKET waits.

And in case you aren’t quite ready to solve quantum computing problems just yet, here’s Kendra talking about the costing models for parallel queries.

Return to sp_Blitz or Ask Us Questions

---

How to Configure ‘Max Degree of Parallelism’ and ‘Cost Threshold’ in SQL Server

The short of it is this:

The defaults are bad. A MAXDOP of 0 means that a query can spread across all the cores in your system, and a cost threshold for parallelism of 5 means it won’t take much to get there. The costing models were created on a computer that it probably couldn’t run the OS on your phone; that’s how long ago it was. So a single query can dominate your server and hamper performance for everything and everyone else.

But there are also problems with incorrect non-default values! With the exception of certain vendor workloads (Dynamics, SharePoint, etc.), a MAXDOP of 1 isn’t really doing you any favors either. In general, setting MAXDOP to an odd number is a bad idea, because you really shouldn’t have an odd number of cores assigned to a CPU. Keep ’em even, folks.

The (sort of) same goes when considering cost threshold for parallelism. If you set it too high, not enough of your queries that could benefit from some parallelism will get it.

TO FIX THE PROBLEM

If you want to skip to the healing, here’s what you can do!

For the clickers:

In SSMS, right click on your instance, and select Properties:

The settings you’re looking for are on the Advanced page, and are helpfully marked down the bottom under the bolded Parallelism header. This is where you fill in the correct values for your environment.

If you’re like me, and you prefer to script things out, here’s another way:

Again, you’ll have to replace the question marks with the correct values for your environment.