Sql-server – SQL Server performance issue (CXPACKET wait type)

optimizationperformancesql serversql-server-2012wait-types

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

Best Answer

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

Related Question