Quick Scan Report – Max Degree Of Parallelism


The Max Degree of Parallelism (MAXDOP) setting in SQL Server is a configuration option that controls the number of processors utilized in the execution of a single statement in a query, essentially defining the degree of parallelism for the SQL Server instance.

When SQL Server executes a query, it can choose to break it down into smaller tasks that can be run simultaneously across multiple processors to improve performance – a concept known as parallel execution or parallelism. However, there are certain overheads associated with managing and synchronizing these parallel tasks, which could sometimes lead to performance degradation instead of improvement. The MAXDOP setting helps control the degree of parallelism to prevent such potential inefficiencies.

Here’s how the MAXDOP value affects SQL Server’s behavior:

  • A MAXDOP value of 0 (the default) allows SQL Server to use all available CPUs up to a maximum of 64 for parallel execution.
  • A MAXDOP value of 1 means that parallel plan execution is not allowed, and each query will be executed using a single CPU.
  • A MAXDOP value greater than 1 means that SQL Server can use no more than that many CPUs for parallel execution of a single query.

It’s worth noting that the MAXDOP setting doesn’t guarantee a specific degree of parallelism but only provides an upper limit. The SQL Server query optimizer decides whether a query will benefit from parallel execution based on several factors, such as query complexity and the estimated cost of the execution plan.

Setting the appropriate MAXDOP value is important for balancing system performance and resource utilization. The optimal value depends on a variety of factors including the SQL Server workload, number of CPUs, and server’s other responsibilities. Prior to about 2020 Microsoft recommended for OLTP (Online Transaction Processing) workloads that MAXDOP be set to a value no higher than the number of cores in a single CPU socket, or no higher than 8, whichever is lower. After 2020, those numbers are a bit higher, sometimes up to 16 or the number of cores in a single CPU socket.

It’s also important to note that MAXDOP can be configured at the server level, affecting all queries, or can be overridden for individual queries using the MAXDOP query hint. This allows flexibility for tuning the performance of specific queries as needed.

The Max Degree of Parallelism setting can be accessed on the server properties dialog in SQL Server Management Studio.

MAX Degree Of Parallelism

If your server has 4 cores, you could use the following script to set max degree of parallelism to match your 4 cores.


EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 4;
GO
RECONFIGURE WITH OVERRIDE;
GO

The recommendation is to set this to match the number of cores on your system, or the maximum number of cores in any NUMA node.

Setting this to 1 tells SQL Server to disable parallel processing, and is generally not recommended.