How do you force the maxdop in the App plan to match the SSMS plan ?

Question

How do you force the maxdop in the App plan to match the SSMS plan ?

isuckatsql

SSChampion

Points: 10381
More actions
May 17, 2013 at 7:31 am

#272819

From SSMS Plan:
<StmtSimple StatementSubTreeCost="329.661" StatementEstRows="9" StatementOptmLevel="FULL" QueryHash="0x8D96B6E9E59734B3" QueryPlanHash="0xB33E59170E9A855A">
<QueryPlan DegreeOfParallelism ="8" MemoryGrant="3263496" CachedPlanSize="280" CompileTime="160" CompileCPU="160" CompileMemory="4248">
From App Plan:
<StmtSimple StatementSubTreeCost="6.74389" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0x8D96B6E9E59734B3" QueryPlanHash="0x44E50B9406BB5E36" StatementOptmEarlyAbortReason="TimeOut">
<QueryPlan DegreeOfParallelism ="1" MemoryGrant="5120" CachedPlanSize="240" CompileTime="100" CompileCPU="100" CompileMemory="4048">
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Maxdop is just that, Maximum degree of parallelism, the maximum number of cores that the query can use. A query with MAXDOP 8 can run on a single core if the optimiser decides for a serial plan and it's the execution engine at run time that decides what number of cores to use when it gets a parallel plan.

What's the actual problem here? Inefficient query plan?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

isuckatsql SSChampion Points: 10381 More actions · Answer 2

The query runs in approx. 700 ms with Maxdop 8 and in about 5 seconds with Maxdop 1.

I need to force the App plan to use Maxdop 8 instead of Maxdop 1.

Allowing SQL to choose with a default Maxdop value of 0 is still very slow!

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As I said, MAXDOP is just the maximum number of processors and MAXDOP 0 means use up to the total number of processors, but it's up to the optimiser and execution engine as to how many exactly they use, up to the specified maximum. You're asking how to force the minimum number of processors, which you can't.

Don't look at the degree of parallelism, look at the complete execution plan and see what's different.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

And look to the Cost Threshold for Parallelism. Compare that with the costs of the plans themselves. It could be that with different parameters compiled a different times, you're getting different cost estimates that leads to parallel plans or to serialized plans, just based on that threshold.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Grant Fritchey (5/17/2013)
And look to the Cost Threshold for Parallelism. Compare that with the costs of the plans themselves. It could be that with different parameters compiled a different times, you're getting different cost estimates that leads to parallel plans or to serialized plans, just based on that threshold.

Indeed, and if you look at the plan snippet, the cost estimation on the second one is way, way lower than the first, probably low enough that the optimiser decided parallelism wasn't worth it. The question is why the costs are so different and that requires examining the entire plan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

isuckatsql SSChampion Points: 10381 More actions · Answer 6

..... and so the digging continues.

Thanks for the feedback !