Query plan XML: one stmt's DegreeOfParallelism is 1 for any maxdop other than 1 (maxdop 1 leads to DegreeOfParallelism = 0 with reason MaxDopSetToOne)

Question

I have a procedure with two SELECTs. One statement respects the server's/database's/query hint's maxdop setting and uses parallelism, the other is being difficult and never going parallel.

The 8 core server and this db are configured for maxdop 0 (not great, and I can change it, but I'd like to leave it and figure out what's going on). Cost threshold is 5.

Auto update stats is on and stats on the table are showing as being updated earlier today. I may try updating the stats on the table manually after hours tonight.

This server and db are an AG secondary replica. The same procedure on the primary runs fast and both statements go parallel. The primary has more cores, and its maxdop is set explicitly to to 8.

Statement 1 query hints

• No query hint -> DegreeOfParallelism = 1
• Maxdop 0 -> DegreeOfParallelism = 1
• Maxdop 1 -> DegreeOfParallelism = 0 (reason MaxDopSetToOne)
• Maxdop 8 -> DegreeOfParallelism = 1
• Maxdop 7 -> DegreeOfParallelism = 1

Statement 2 query hints

• No query hint -> DegreeOfParallelism = 8
• Maxdop 0 -> DegreeOfParallelism = 8
• Maxdop 1 -> DegreeOfParallelism = 0 (reason MaxDopSetToOne)
• Maxdop 8 -> DegreeOfParallelism = 8
• Maxdop 7 -> DegreeOfParallelism = 7

Thanks for any help!

Answer 1

it's maxdop, not mindop

Setting MAXDOP at any level only sets an upper limit on how parallel a query can go. It does not instruct a query to go parallel; a query still has to:

• Have the estimated cost qualify it for a parallel plan
• Not be a trivial plan
• Not have any constructs that force it to run single-threaded (scalar UDFs, inserting into a @table variable, etc.)
• Have the parallel plan's estimated cost be lower than the serial plan's estimated cost

enforcer

If you want to try to force a parallel plan, you'll need to

• Use trace flag 8649 at the session or query level as a hint
• If you're on a newer version of SQL Server, you can use the ENABLE_PARALLEL_PLAN_PREFERENCE USE hint.

It should be noted here that neither of those options is officially supported, and should probably only be used for testing and experimentation.

This would probably be the most enlightening thing for you, though, since it will show you the estimated cost of a parallel plan and if it's higher than the serial plan's estimated cost, or if there's still something locally inhibiting parallelism.