MSSQL Parallel Processing Configuration

Databases

SQL Server

SQL Configuration

Cost Threshold for Parallelism

Check

Set

MAXDOP (Maximum Degree of Parallelism)

Bibliopgraphy

SQL Lifecycle

MSSQL Wait Events

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

Bibliopgraphy

(2) https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option(1) https://docs.aws.amazon.com/prescriptive-guidance/latest/sql-server-ec2-best-practices/maxdop.html(3) https://docs.aws.amazon.com/prescriptive-guidance/latest/sql-server-ec2-best-practices/maxdop.htmlhttps://sqlperformance.com/2013/10/sql-plan/parallel-plans-branches-threads (Paul White, 2013)