The Perils Of Change: Max Degree Of Parallelism

Downtune

---

Like in yesterday’s post about Cost Threshold For Parallelism, changing MAXDOP settings will have a universal effect on the workload.

This is true whether you change it at the server level for all databases, or at the database level using a database scoped configuration for a single database.

It is a guardrail to prevent unwanted conditions as a whole, like excessive concurrent parallel queries causing worker thread starvation (THREADPOOL waits), or just pushing CPU to 100% for extended periods of time.

You may also find times when parallel threads within a single query are more prone to involvement in blocking or deadlocking.

The problem is similar to yesterday’s post, and requires some level of attention to detail at the query level after you change MAXDOP.

Don’t just look at all the various CX waits, pronounce that there are fewer of them, and call the job done.

You should examine parallel query metrics to make sure that runtimes are still adquate.

Real World

---

If we take a single query and run it at reduced DOPs, you can see the pattern I’m talking about.

Yesterday, we looked at DOP 8 vs. DOP 1, and I’m including a DOP 1 run here too. It’s just not as important, unless you’re the kind of wackadoo SharePoint admin who changes MAXDOP to 1.

(I’m going to be honest with you here, it’s been YEARS since I’ve seen a SharePoint database, or looked at the SharePoint documentation to see if the MAXDOP 1 requirement still exists).

When you change DOP, it’s up to you to monitor (you can use Query Store for this) execution and CPU time to look for big regressions.

Keeping the above picture in mind:

• Is it okay if this query runs for ~300ms longer at DOP 4?
• Is it okay if this query runs for ~1.3 seconds longer at DOP 2?

Taking a full three seconds longer at DOP 1 is probably out the door for queries that users care about, but that’s more likely from changing Cost Threshold For Parallelism.

A diligent caretaker for SQL Server may even look for critical queries that currently use parallel execution plans, and time them with a lower DOP to make sure there’s no substantial regression before making the change.

If there’s a big increase in duration, add the higher DOP hint to queries where it makes sense.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.