Max Degree of Parallelism Not Optimal

The max degree of parallelism (MAXDOP) setting limits how many processors SQL Server can use to execute a single query. This setting is critical for optimizing performance, as it determines how well SQL Server can parallelize queries across CPU cores. If the setting is too low, SQL Server can’t make full use of available resources, but if it’s set too high, excessive parallelism can lead to CPU contention, slowing down performance.

In this post, we’ll explain why MAXDOP is important, how it impacts SQL Server performance, and how Aireforge automatically detects and helps resolve issues related to this setting.

What Happens When MAXDOP Is Misconfigured?

  • Limited Parallelism: Setting MAXDOP too low prevents SQL Server from distributing query workloads across all available CPU cores. This can slow down complex queries that would benefit from more parallel execution.

  • Excessive Parallelism: Setting MAXDOP too high can overwhelm the server with too many concurrent query threads, causing CPU contention and reducing overall performance, especially under heavy workloads.

Why We Recommend Setting MAXDOP to 8

A common recommendation is to set MAXDOP to 8, particularly for cloud and virtual environments. You may wonder why 8 is suggested even when the system has fewer cores. Here’s the reasoning:

  • Max as a Cap: MAXDOP acts as a maximum value, meaning that if a system only has 4 cores, it will use up to 4 cores, even if MAXDOP is set to 8. This doesn’t hurt performance; it simply ensures that if the system is scaled up in the future, you won’t need to manually adjust MAXDOP to match the increased core count.

  • Default in Azure SQL: Microsoft has made MAXDOP 8 the default for Azure SQL Database as it’s a versatile and safe option. Even if you have an instance with only 1 or 2 cores, the MAXDOP remains 8, preventing excessive parallelism while preparing for future scalability.

💡 Example: In cloud environments, where virtual machines (VMs) are often resized, having MAXDOP set to 8 means that when you increase the size of the VM, the system automatically makes use of the additional CPU cores without requiring you to adjust the MAXDOP setting.

While in static environments, such as on-premises servers, setting MAXDOP to the exact core count is fine, this can lead to confusion in dynamic cloud setups. Using 8 as a cap ensures that your SQL Server instance remains scalable without needing constant manual adjustments.

How Aireforge Detects MAXDOP Issues

Aireforge automatically checks your MAXDOP configuration and compares it to your SQL Server’s CPU setup. Here’s how the process works:

  1. CPU Core and NUMA Analysis: Aireforge analyzes the number of logical cores and NUMA nodes (Non-Uniform Memory Access) in your system. Based on this, it calculates an optimal MAXDOP value.

  2. Version-Specific Guidelines: Aireforge applies different recommendations based on the SQL Server version. For SQL Server 2016 and earlier, Microsoft generally recommends setting MAXDOP to 8 for systems with more than 8 logical processors. For SQL Server 2017 and later, systems with multiple NUMA nodes and more than 16 logical processors per NUMA node may benefit from MAXDOP values up to half the number of logical processors per NUMA node, with a maximum of 16.

  3. Current vs. Recommended MAXDOP: Aireforge compares your current MAXDOP setting against its recommendation. If the current setting is too low or too high based on the detected cores, it flags this as an issue.

  4. Script Generation: If MAXDOP is set incorrectly, Aireforge generates a script to adjust the setting in line with best practices, ensuring optimal performance without the risk of CPU contention.

How to Change the Max Degree of Parallelism for SQL Server (Database Level)

USE [WhippetWorks]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;
GO

How to Change the Max Degree of Parallelism for SQL Server (System-Wide)

EXEC sys.sp_configure N'show advanced options', N'1';
RECONFIGURE;
GO
EXEC sys.sp_configure N'max degree of parallelism', N'8';
GO
RECONFIGURE;
GO

What Is the Default MAXDOP for Azure SQL Database?

💡 As of August 2020, new Azure SQL databases and elastic pool databases have a default MAXDOP value of 8. This ensures optimal performance without excessive parallelism.

Microsoft explains: "Our telemetry data and experience running Azure SQL Database shows that MAXDOP 8 is the optimal value for most workloads. It reduces the risk of performance problems due to excessive parallelism while still allowing queries to execute faster by using more threads."