Can You Tell Why a Plan Didn’t Go Parallel?

Let’s face it, most of the time, you probably don’t want your SQL Server execution plans to go parallel. After all, that’s why, instead of adjusting the Cost Threshold for Parallelism (as you should have), you’ve set the Max Degree of Parallelism to 1 (I see you out there). However, some of you recognize that, in fact, some queries need to run in Parallel.

Yet, sometimes, a query you think should run in parallel doesn’t. Can you tell why a plan didn’t go parallel?

If It’s Not Parallel, Why Not?

The good news is, this question is really simple to answer. I’m sure you’ll be shocked when I tell you that it’s right there in the execution plan. The bad news is, you’re going to have to open up the Properties sheet for the execution plan. I know. I hear you. The horror. I promise, I’ll help you through this.

Let’s start with a query. Since, for our purposes here, I don’t care if the silly thing goes parallel, I’m going to take a really simple query for our example:

SELECT soh.OrderDate,
       soh.ShipDate,
       sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
WHERE sod.OrderQty > 30;

However, I’m also going to do a little cheating. I’m going to change the properties on my server. First, I’ll set the Cost Threshold for Parallelism to 1 from the default of 5. Next, I’ll set MaxDOP to 1 (just like your servers). I’m changing the Cost Threshold so that even my silly query will qualify for parallelism with a cost around 1.5. The other, well, I’ll tell you in a second.

I don’t have to execute the query. I can use the execution plan without runtime metrics, or, I can execute the query and capture the execution plan with runtime metrics (aka, estimated and actual plans). Either way, here’s a representation of the plan captured:

Yeah, yeah, missing index suggestion, blah, blah, blah. Let’s right click the plan and select “Properties” from the context menu. You should see something that looks like this appear, by default, on the right side of your screen:

In this instance, that’s the properties for the query itself. I want to click on the first operator, the SELECT operator, to see the properties there. Sometimes, especially if you’ve been working with plans and you already have properties open, when you go to the plan, the first operator may be selected, but the property sheet can still be the query properties, as above. In that case, click on a different operator, then click on the SELECT operator (that was the trickiest part of this entire process). You should see something like this:

And there it is. The property labeled ‘NonParallelPlanReason’. The value in this case is not shocking ‘MaxDOPSetToOne.’

That’s it. We’ve answered the question at the top of the blog post. You can absolutely identify why a given query, that in all other ways qualifies to go parallel, did not. The list pretty much consists of all the reasons why plans can’t go parallel:

• MaxDOPSetToOne
• NoParallelDynamicCursor
• NoParallelFastForwardCursor
• NoParallelCreateIndexInNonEnterpriseEdition
• NoParallelPlansInDesktopOrExpressEdition
• TSQLUserDefinedFunctionsNotParallelizable
• CLRUserDefinedFunctionRequiresDataAccess
• NoParallelForMemoryOptimizedTables

Conclusion

Permit me, for just a moment, to bash on PostgreSQL, just a tiny bit. One thing I miss over there is the instrumentation we get on SQL Server. Sure, this is relatively trivial stuff, but SQL Server provides us a way to see into the internals a bit without opening the debugger. First of all, I’m just not smart enough to open the debugger and walk through the code. But, more importantly, it’s not something I’d want to do in a production system anyway.

Now you know. Can you tell why a plan did not go parallel? Yes. If you have a plan that you think should have gone parallel (because you don’t have MaxDOP set to 1), but you’re unsure why, here’s where you can find an answer and, maybe, do something about it.

Please, please, please, keep those questions coming.