patternsqlModerate
Why is cost threshold for parallelism ignored?
Viewed 0 times
whyignoredthresholdforparallelismcost
Problem
SQL Server 2012 SP2 Enterprise Edition. Users complaining of slowness. Monitoring tool shows highest wait event is
Instance settings
Ran
Anyone ever see Cost Threshold for Parallelism being ignored?
CXPACKET. Instance settings
- MAXDOP: 8
- Cost Threshold for Parallelism: 175
Ran
sp_BlitzCache (from the Brent Ozar toolset, those guys rock) for further diagnosis and results show queries with cost under 175 going parallel. Anyone ever see Cost Threshold for Parallelism being ignored?
Solution
Anyone ever see Cost Threshold for Parallelism being ignored?
It is not being ignored. During the compilation process, the optimizer first considers a serial plan. If the estimated cost of that plan exceeds the Threshold, the optimizer goes on to look for a parallel plan. If the resulting parallel plan is costed below the best serial one, it will be chosen.
So, the parallel plan will have a lower cost that the serial one (which you cannot see). It is perfectly possible for the final parallel plan to have an estimated cost below the Threshold - the point is the best serial plan candidate exceeded the Threshold.
An example can be seen in my blog post on parallel plan bitmaps.
It is not being ignored. During the compilation process, the optimizer first considers a serial plan. If the estimated cost of that plan exceeds the Threshold, the optimizer goes on to look for a parallel plan. If the resulting parallel plan is costed below the best serial one, it will be chosen.
So, the parallel plan will have a lower cost that the serial one (which you cannot see). It is perfectly possible for the final parallel plan to have an estimated cost below the Threshold - the point is the best serial plan candidate exceeded the Threshold.
An example can be seen in my blog post on parallel plan bitmaps.
Context
StackExchange Database Administrators Q#81980, answer score: 13
Revisions (0)
No revisions yet.