5

I have a performance issue with production SQL Server 2012 which has 24 cores and 32 GB of RAM. From wait type I can see CXPACKET is on the top based on the following SQL Server metrics, should I reduce MAXDOP or "cost Threshold for Parallelism" to avoid more parallelism and reduce CXPACKET wait type?

Any help would be appreciated.

enter image description here

Improve this question
6
  • 1
    CXPACKET waits are not actually a harmful waits they are more of indicators and normally wait just below this wait is what causing the issue. Now what is MAXDOP you have set as of now. Also run Query mentioned in This SE Thread to get correct value that you should use for MAXDOP and tell it to me.
    – Shanky
    Mar 2, 2017 at 5:03
  • 1
    reducing cost threshold for parallelism will make it worse, not better. Have you changed from the default Cost Threshold 5 and MAXDOP 0? They're probably not good defaults for your server.
    – Greg
    Mar 2, 2017 at 5:47
  • Please read this : scarydba.com/2017/02/20/estimated-costs-queries maybe this will help you in setting a more properly Cost Threshold for || and there is one more scarydba.com/2017/02/28/… (for 2016)
    – Sabin B
    Mar 2, 2017 at 6:41
  • Thanks, Shanky. the current value is 0 which means unlimited, and the recommended query returned 5 as MaxDop value, so should I update the maxdop to 5? I'm wondering if the number of full scan/s is usual or not.
    – Sam
    Mar 2, 2017 at 7:52
  • What do you think about cost Threshold for Parallelism setting? any recommendation?
    – Sam
    Mar 2, 2017 at 7:53

1 Answer 1

Reset to default
-1

General consensus seems to tend torwards setting Cost Threshold for Parallelism to 50 and go from there. Why Cost Threshold For Parallelism Shouldn’t Be Set To 5

This even includes official reccomendations from VMWare regarding SQL Server Setup with vSphere. Section 4.5 of the Guide "Architecting Microsoft SQL Server on VMware vSphere®" states:

The default value is 5 which is usually considered too low. [...] The recommendation instead is to increase the CTFP value from 5 seconds to approximately 50 seconds to make sure only large queries run in parallel. Set the MAXDOP according to Microsoft’s recommendation for the number of cores in the VM’s NUMA node (no more than 8).

The usage of of the unit "seconds" should be taken with a grain of salt because on modern systems and versions of SQL Server it will not reflect the actual duration in seconds.

Regarding MAXDOP there is a comprehensive guide found here: Configure the max degree of parallelism

So in your case I would start with the following settings and test them out:

Cost Threshold for Parallelism: 50

Maximum Degree of Parallelism: 8

Improve this answer
6
  • @KrisGruttemeyer -- what you edited out was a quote; you probably should revert your edit and add a comment. Source, page 48.
    – mustaccio
    Jul 10, 2017 at 17:25
  • I understand it's a quote, but it's also not technically correct. Query cost units are not seconds, it's unitless.
    – Kris Gruttemeyer
    Jul 10, 2017 at 17:27
  • You cannot arbitrarily change a quote -- because it's a quote. Besides, the horse's mouth says this: "The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration." How'bout that?
    – mustaccio
    Jul 10, 2017 at 17:29
  • I'll revert my change but also, per Brent Ozar: "They’re still a valuable reference metric, but they are, even for actual plans, still an estimated cost. Pay attention to them. Pay attention to the fact that THEY DON’T MEAN SECONDS ANYMORE." brentozar.com/archive/2017/03/…
    – Kris Gruttemeyer
    Jul 10, 2017 at 17:34
  • @KrisGruttemeyer -- Thank you for trying to clarify my post. I do agree with your intention. But since this is a direct quote I will revert it.
    – Thomas Kronawitter
    Jul 10, 2017 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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