MSSQL Parallel Processing Configuration

CXCONSUMER, CXPACKET

Cost Threshold for Parallelism

Check

USE [master]

GO

EXEC sp_configure 'show advanced options', 1 ;  

GO  

RECONFIGURE  

GO  

EXEC sp_configure 'cost threshold for parallelism'

Set

USE [master]

GO

EXEC sp_configure 'show advanced options', 1 ;  

GO  

RECONFIGURE  

GO  

EXEC sp_configure 'cost threshold for parallelism', 50

GO  

RECONFIGURE 

GO 

MAXDOP (Maximum Degree of Parallelism)

Check

USE [master]

GO

EXEC sp_configure 'show advanced options', 1 ;  

GO  

RECONFIGURE  

GO  

EXEC sp_configure 'max_degree_of_parallelism' 

Setting MAXDOP to 2, 4, or 8 generally provides the best results in most use cases. We recommend that you test your workload and monitor for any parallelism-related wait types such as CXPACKET.  (1)
SQL Server 2019 (15.x) introduces automatic recommendations for setting the MAXDOP server configuration option during the installation process based on the number of processors available (2)

AWS recommend (1)...

           | Logical     |NUMA Nodes | Processors  |  MAXDOP-----------+-------------+----------------------Single     | =< 8        |  4, 2, or no of cores (for 1 or 2 cores)Single     |  > 8        |  8, 4, or 2Multiple   | =< 16       |  8, 4, or 2Multiple   |  > 16       | 16, 8, 4, or 2
To check your NUMA node count...SELECT @@SERVERNAME,       SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),       cpu_count,        hyperthread_ratio,        softnuma_configuration,        softnuma_configuration_desc,        socket_count,        numa_node_count   FROM sys.dm_os_sys_info 
cpu_count - number of logical CPUs in the system.hyperthread_ratio - ratio of the number of cores that are exposed by one physical processor.softnuma_configuration - 0 (OFF), 1 (automated): soft-NUMA, or 2 (manual): soft-NUMAsoftnuma_configuration_desc is OFF, ON (SQL Server automatically decides the NUMA node size), or MANUAL (soft-NUMA is manually configured)socket_count is the number of processor sockets.numa_node_count is the number of NUMA nodes available in the system.

Set

USE mydatabase ;  

GO   

EXEC sp_configure 'show advanced options', 1;  

GO  

RECONFIGURE WITH OVERRIDE;  

GO  

EXEC sp_configure 'max degree of parallelism', 8;  

GO  

RECONFIGURE WITH OVERRIDE;  

GO

Where 8 is the new value

Unlimited (default)...

EXEC sp_configure 'max degree of parallelism',0