What is Max Degree of Parallelism?
Degree of parallelism is the number of processors that are assigned to run a single SQL statement. SQL Server manages to detect the best degree of parallelism for each query to generate the execution plan. SQL Server may use all the procedure in case if it required. If you do not want SQL Server to use all of the installed processors in your operations, you can limit the use of processors by configuring the processor value to a lower number than the actual number of processors installed. For instance, if a server has four processors and you want to use only three for parallelism, then you should set the max degree of parallelism option to 3.
The default option, 0, uses the actual number of processors installed in the system for the parallel execution plans of queries and index operations.
sp_configure 'max degree of parallelism', 6; GO RECONFIGURE WITH OVERRIDE; GO
Maxdop is a query hint. When you want to use a specific number of processors for an individual query or index operation, you can use the MAXDOP query hint or index option to provide better control. MAXDOP overrides the settings defined in the max degree of parallelism option. The use of MAXDOP is limited to the execution of a query that contains the MAXDOP query hint or index option.
SQL Server 2016 introduces a new option to limit the number of processors assigned for the DBCC CHECKDB statement, overriding the instance level MAXDOP configuration. You can use this option in case if you want to use all the compute power of the server or you want to use the minimum compute power of the server.
In the below example I am specifying the MAXDOP hint as 5 which will internally asks Database Engine to generate the execution plan to use the 5 processors.
select @@SPID DBCC CHECKDB (AdventureWorks2012) WITH MAXDOP = 5;
You can check the number of processors user by SQL Server by executing the below query.
select Session_id , scheduler_id from sys.dm_os_tasks where session_id = <SPID>
You can execute the below script to identify the optimal MAX DOP setting and set the MAX DOP as using SP_Configure procedure.
select case when cpu_count / hyperthread_ratio > 8 then 8 else cpu_count / hyperthread_ratio end as optimal_maxdop_setting from sys.dm_os_sys_info; -- Script to set the maxDOP sp_configure 'show advanced options', 1; GO RECONFIGURE WITH OVERRIDE; GO sp_configure 'max degree of parallelism', <OutPut of the above script>; GO RECONFIGURE WITH OVERRIDE; GO