Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

Applies to: SQL Server 2008SQL Server 2008 R2SQL Server 2012 More

Summary


The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. This option determines the number of threads that are used for the query plan operators that perform the work in parallel. Depending on whether SQL Server is set up on a symmetric multiprocessing (SMP) computer, a non-uniform memory access (NUMA) computer, or hyperthreading-enabled processors, you have to configure the max degree of parallelism option appropriately. This article discusses the general guidelines that you can use to configure the max degree of parallelism option for SQL Server when you use the sp_configure system stored procedure.

The OPTION (MAXDOP) Transact-SQL query hints can override the max degree of parallelism option in the sp_configure value if the query specifies this option. In SQL Server 2008 and later versions, if the MAXDOP value exceeds the value that was configured by using the Resource Governor, the database engine uses the Resource Governor MAXDOP value. All semantic rules that are used with the max degree of parallelism option are applicable when you use the MAXDOP query hint. The other two options that can override or affect the MAXDOP setting are as follows: 
  • Soft NUMA
  • Parallel index operations

The max degree of parallelism configuration option does not limit the number of processors that SQL Server uses. To configure the number of processors that SQL Server uses, use the affinity mask configuration option.

The MAXDOP setting applies to each operator of the plan (e.g. Clustered Index Scan). Each step will use one CPU or the number of CPUs that is specified by MAXDOP and never anything in between. If multiple operators of a plan execute concurrently in parallel, the total number of threads that are used by the query may exceed the MAXDOP setting specified. 

More Information


The manual configuration of max degree of parallelism depends on the number of logical processors in a NUMA node as well as the processor layout. When SQL Server starts, the SQL OS boot process prints the processor layout recognized and used.

You can also use the Dynamic Management views sys.dm_os_sys_info and sys.dm_os_nodes for information about the number of processors that are used by SQL Server and the number of NUMA nodes that are recognized by SQL Server.  

SQL Server 2016 and higher

Starting with SQL Server 2016, during service startup if the Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. The Database Engine takes care of placing logical processors from the same physical core into different soft-NUMA nodes. The recommendations in the table below are aimed at keeping all the worker threads of a parallel query within the same soft-NUMA node. This will improve the performance of the queries and distribution of worker threads across the NUMA nodes for the workload. 

Server with single NUMA node Less than 16 logical processors Keep MAXDOP at or below # of logical processors
Server with single NUMA node Greater than 16 logical processors Keep MAXDOP at half the number of logical processors with a MAX value of 16 
Server with multiple NUMA nodes Less than 16 logical processors per NUMA node Keep MAXDOP at or below # of logical processors per NUMA node
Server with multiple NUMA nodes Greater than 16 logical processors per NUMA node Keep MAXDOP at half the number of logical processors per NUMA node with a MAX value of 16 

Note: NUMA node in the above table refers to soft-NUMA nodes automatically created by SQL Server 2016 and higher. 

Note: Use these same guidelines when you set the max degree of parallelism option for Resource Governor workload groups.  

SQL Server 2008 to SQL Server 2014

Use the following guidelines when you configure the MAXDOP value for the SQL Server instance.

Server with single NUMA node Less than 8 logical processes Keep MAXDOP at or below # of logical processors
Server with single NUMA node  Greater than 8 logical processors Keep MAXDOP at 8
Server with multiple NUMA nodes Less than 8 logical processors per NUMA node Keep MAXDOP at or below # of logical processors per NUMA node
Server with multiple NUMA nodes Greater than 8 logical processors per NUMA node Keep MAXDOP at 8

The maximum value of 8 that is mentioned in these guidelines is applicable for typical SQL Server activity and the overhead for the exchange operators that are used in parallel query plans. You can vary this maximum value, depending on your specific application patterns and the concurrent activity on the instance of SQL Server. For example, consider the following situations:

  • If you have very small number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a larger value. For example, you can set the MAXDOP value to 16.
  • If you a have very large number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a smaller value. For example, you can set the MAXDOP value to 4. 

Note Any value that you consider using should be thoroughly tested against the specific application activity or pattern of queries before you implement that value on a production server. 

Additional Information