Max degrees of parallelism (a.k.a MAXDOP) in SQL Server is used to control the maximum number of threads per execution plan operators work.MAXDOP does not restrict the number of CPU’s/Threads used by single batch or Query.
Ideally MAXDOP should be equal to number of online schedulers per node. You can use the below query to get the number of online schedulers per node. All the parallel threads for the tasks of the query will be assigned from schedulers of same node so having MAXDOP beyond the number of online schedulers per node may not really improve the performance (With some exceptions).
select count(*) as Maxdopcount, parent_node_id from sys.dm_os_schedulers where status='VISIBLE ONLINE' group by parent_node_id
Depending upon the workload in your environment you may increase or decrease the value.
Note: Always ensure you have same number of online schedulers in each node else you may face uneven workload and memory distribution among the SQL Server schedulers more details in SQL Server NUMA load distribution.
To configure Max degree of parallelism follow the below steps.
1.Connect to the Database Engine.
2.From the Standard bar, click New Query.
3.Then execute the following query.
Syntax:</pre> sp_configure 'show advanced options', 1; GO RECONFIGURE WITH OVERRIDE; GO sp_configure 'max degree of parallelism', 1; /*Replace 1 with your preferred MAXDOP value */ GO RECONFIGURE WITH OVERRIDE; GO <span style="font-family: Consolas, Monaco, monospace; font-size: 12px; line-height: 18px;">
1.In Object Explorer, right-click a server and select Properties.
2.Click Advanced from select a page.
3.In the Max Degree of Parallelism box, select the maximum number of processors to use in parallel plan execution.