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.
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.
- The product documentation about this option can be found at max degree of parallelism Server Configuration Option
Automatic Soft NUMA is explained in the documentation topic: Automatic Soft-NUMA
For best practices information when you are running an instance of SQL Server on computers that have more than 64 CPUs, see the following SQL Server Books Online topic: Best Practices for Running SQL Server on Computers That Have More Than 64 CPUs
- For more information about the MAXDOP setting that is enforced by using Resource Governor, review the information in the Dynamic Management view sys.dm_resource_governor_workload_groups and the following post on SQL Server Support team's blog: Server’s “Max Degree of Parallelism” setting, Resource Governor’s MAX_DOP and query hint MAXDOP–which one should SQL Server use?
- For more information about specifying custom DOP values for index operations, refer to the documentation: Configure Parallel Index Operations