5

SQL Server 2012 SP2 Enterprise Edition. Users complaining of slowness. Monitoring tool shows highest wait event is 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?

  • Are the plans older than the time when the parameter was changed? Just a guess but I would assume changing the threshold is not a reason for a recompile. – James Z Nov 5 '14 at 20:33
  • Changing the MAXDOP/CTP settings does blow away the plan cache, though. So, every query would have to face a recompile before it goes into cache again. – Kris Gruttemeyer Nov 5 '14 at 20:41
  • I should have mentioned that the value of 175 for CTfP has been set for a number of months, as well as MAXDOP 8. – Roberto Nov 5 '14 at 20:48
  • 2
    It's perfectly possible for the plan to be costed above the threshold for parallelism, go onto the next stage of optimization and then end up with a parallel plan at the end of that stage that is cheaper than the threshold. See for example sqlkiwi.blogspot.com/2011/07/bitmap-magic.html – Martin Smith Nov 5 '14 at 21:53
7

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.

3

I'm pretty sure that sp_BlitzCache will show you the cost of the actual plan used for your queries, whereas the server will considers a parallel plan when the initially estimated cost exceeds the threshold value.

A noticable difference between estimated and actual query plan costs could happen if you have stale/bad statistics on your tables.

If you've identified a specific query that you want to run serially, you can add the following OPTION at the end of the statement - this will ensure a serial plan:

SELECT something
FROM somewhere
OPTION (MAXDOP 1);

Another option is to use the resource governor to control parallelism.

  • Stats are collected weekly using Ola Hallengrin's maint scripts, but that doesn't mean they haven't gone stale at that particular point of runtime. This is a good point. Thanks, I'll investigate the estimated vs actual cost. – Roberto Nov 5 '14 at 20:53
-2

I,

It doesn't ignore!. setting this parameter is only saying: The optimizer uses that cost threshold to figure out when it should start evaluating plans that can use multiple threads. for example : let say, your sql server got 4 threads, 3 of those threads are in use,soo only 1 left to use . if at this time you running your query, than it doesn't matter if you parameter is set to 5 or 500. in that case your query will get 1 thread (no parallelism) even if the optimizer will get COST of 1000 .

  • I think he's going for the inverse here. Why would a a query with LESS than the CTP go parallel? – Kris Gruttemeyer Nov 5 '14 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.