MAXDOP (Degree of Parallelism)

Adjusting MAXDOP can optimize parallel queries, balancing the load on the server.

You can use it directly in the query or change the settings in the DBMS (Recommended)

How to Set the MAXDOP Value at the Server Level

USE <database_name>; 
GO 
EXEC sp_configure 'show advanced options', '1'; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO 
EXEC sp_configure 'max degree of parallelism', '16'; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO  

How to Set the MAXDOP Value at the Workload Group Level

CREATE WORKLOAD GROUP <group_name> 
WITH 
    (MAX_DOP = 4) 
USING <pool_name>; 
GO  

How to Set the MAXDOP Value at the Database Level

USE <database_name>; 
GO 
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 16; 
GO  

How to Set the MAXDOP Value at the Query Level

SELECT * 
FROM <table> 
OPTION (MAXDOP 2); 
GO  

Below are Microsoft’s recommendations for MAXDOP values for SQL Server 2016 and on.

For a server with a single NUMA node

• Set the MAXDOP value equal to or less than the number of logical processors if the server has less than or equal to eight logical processors.
• Set the MAXDOP value to 8 if the number of logical processors is greater than eight.

For a server with multiple NUMA nodes

• Set the MAXDOP value equal to or less than the number of logical processors per NUMA node if the server has less than or equal to sixteen logical processors per NUMA node.
• Set the MAXDOP value to half the number of logical processors if the processors exceed sixteen per NUMA node. At maximum, the MAXDOP value should be 16.