Mehedi's Earth

When it comes to SQL Server performance tuning, few settings spark as much debate as MAXDOP—Maximum Degree of Parallelism. This small but powerful configuration controls how many processors SQL Server can use to execute a single query in parallel. While parallelism can speed up queries, it can also lead to unexpected issues like CPU pressure, query blocking, or inconsistent performance across workloads.

In this article, we will explore what MAXDOP really does, why it matters, and how to configure it wisely to strike the right balance between speed and stability in your SQL Server environment.

What is MAXDOP

When SQL Server Optimizer opts for parallel processing, the MAXDOP (Maximum Degree of Parallelism) dictates how many CPU cores can be used by a single query for parallel execution. The default setting (MAXDOP = 0) allows queries to use all available cores. It can exhaust the CPU. When SQL Server runs a query using multiple CPUs (parallelism) then some threads have to wait for others to finish. Which creates high CXPACKET waits. 

Why it Important

The MAXDOP value has impacts on the performance of your queries. A wrong value could result in performance degradation. If the value is too small, a large single queries will take longer time to execute. In flip side, for the large value, multiple queries can exhaust the server. Choosing the right value is crucial.

Considerations

Note that MAXDOP settings is an advanced option. It should be changed only by experienced DBAs or certified SQL Server professionals. In general:

• If affinity mask is not set to default, it may restrict how many processors SQL Server can use on Symmetric Multiprocessing Systems (SMP).
• MAXDOP = 0, SQL Server can use all available processors (up to 64); this is usually not recommended.
• MAXDOP = 1, disables parallelism (runs queries on a single processor).
• MAXDOP = 1 to 32,767, sets the max number of processor cores used per single query execution.
• If the value exceeds the available CPUs, SQL Server automatically uses the actual number of CPUs.
• On single-CPU systems, the MAXDOP setting is ignored.
• MAXDOP limit is per task. A single query can have multiple tasks, each using one worker and one scheduler, up to the MAXDOP value.
• SQL Server 2022 introduced Degree of Parallelism (DOP) Feedback, a feature designed to enhance query performance by detecting and correcting parallelism inefficiencies in recurring queries, based on their elapsed time and wait statistics. As part of the Intelligent Query Processing suite, DOP Feedback helps optimize parallel resource usage and improves overall efficiency for repeated workloads.

For common operations:

• Index operations (create, rebuild, drop clustered index) can be resource-intensive.
  • Override MAXDOP for index operations using the MAXDOP option in the index statement.
  • The MAXDOP value is applied only at execution time and is not saved in index metadata.
  • For details, click here.
• For DBCC CHECKTABLE, DBCC CHECKDB, DBCC CHECKFILEGROUP operation, disable parallelism for these DBCC commands using Trace Flag 2528.

Recommendations from Microsoft

Microsoft has below guideline for picking the right MAXDOP value:

What is NUMA

NUMA means Non-Uniform Memory Access. This is a computer memory design used in multiprocessing, where the memory access time depends on the memory location relative to a processor. Under NUMA, a processor can access its own local memory faster than non-local memory, that is, memory local to another processor or memory shared between processors. For details, click here.

Overriding MAXDOP 

You can override the max degree of parallelism server configuration value:

• At the query level, using the MAXDOP query hint or Query Store hints.
• At the database level, using the MAXDOP database scoped configuration.
• At the workload level, using the MAX_DOP CREATE WORKLOAD GROUP.

Practical Approach

• Before changing, you need to monitor the performance impact before and after of the changing. For monitoring, you can use DBA Dash tool.
• You are considering to change the MAXDOP value because your system has high CXPACKET waits. Please note that CXPACKET waits can have some deeper causes. Try to mitigate them first. For details check my The Waiting Game: Performance Tuning using SQL Server Wait Statistics Part-3.
  
• Identify your top resource hungry queries by running sp_BlitzCache and sort by CPU and by READS. Tune them and observe the performances.
• Note that setting MAXDOP is not enough. You also need to check cost threshold for parallelism of your server. This option dictates the switch between serial to parallel plan execution. SQL Server creates and runs a parallel plan for a query only when the estimated serial plan is higher than the value set in cost threshold for parallelism. The cost refers to an estimated cost required to run the serial plan on a specific hardware configuration, and is not a unit of time. The cost threshold for parallelism option can be any value from 0 to 32767. For details check this.
• Even after setting cost threshold for parallelism and MAXDOP, performance could suddenly get worse. You might have queries earlier that used to go parallel, and now suddenly go serial or vice versa. So, carefully adjust the cost threshold for parallelism value (default 5) to 50 or higher. Otherwise, your small queries may switch from serial to parallel.
• After changing any value, monitor the performance.

Change the Configuration

For MAXDOP

-- Check current settings

SELECT name, value, value_in_use  FROM sys.configurations  WHERE name = 'max degree of parallelism';

-- Setting max degree of parallelism value
EXEC sp_configure 'show advanced options', 1; 
RECONFIGURE; 

EXEC sp_configure 'max degree of parallelism', 4; 
RECONFIGURE;

For cost threshold for parallelism

USE master;
GO

EXECUTE sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXECUTE sp_configure 'cost threshold for parallelism', 10;
GO

RECONFIGURE;
GO

EXECUTE sp_configure 'show advanced options', 0;
GO

RECONFIGURE;
GO

Final Words

Understanding and configuring the MAXDOP (maximum degree of parallelism) setting is essential for getting the most out of SQL Server’s performance. While it can significantly improve query speed and resource utilization when used correctly, it’s important to remember that MAXDOP is not a one-size-fits-all setting. The right configuration depends on your workload, server hardware, and the nature of your queries and operations.

By carefully testing, monitoring, and applying best practices, you can strike the right balance between parallelism and system stability. As new features like DOP Feedback in SQL Server 2022 continue to evolve, they make it even easier to fine-tune performance automatically. Ultimately, knowing when and how to adjust MAXDOP will help ensure that your SQL Server environment runs efficiently and can handle growing demands with confidence.

Going Further

If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too! 

Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.

Let’s make your SQL Server to take your business Challenge!

For any queries, mail to mamehedi.hasan[at]gmail.com.