Query plan XML: one stmt's DegreeOfParallelism is 1 for any maxdop other than 1 (maxdop 1 leads to DegreeOfParallelism = 0 with reason MaxDopSetToOne)
0
votes
1
answer
172
views
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!
Asked by Bobogator
(95 rep)
Jan 24, 2022, 05:31 PM
Last activity: Jul 9, 2025, 12:05 PM
Last activity: Jul 9, 2025, 12:05 PM