Max Degree of Parallelism

The max degree of parallelism (MAXDOP) configuration option limits how many processors can be used during the execution of a query. Having the setting too low can limit SQL Server's ability to benefit from running queries over multiple processors, but having it set too high can cause problems with CPU contention. Other factors include MAXDOP set at a database level, resource governors, and the MAXDOP query hint.

Why did you choose 8 as the new default MAXDOP? Our telemetry data and our experience running the Azure SQL Database service show that MAXDOP 8 is the optimal value for the widest variety of customer workloads. It is a safe option that reduces the likelihood of performance problems due to excessive parallelism, while still allowing queries to execute faster by using more threads.

At the same time, workloads where a different MAXDOP value is optimal do exist. Customers can experiment with different MAXDOP settings to determine what works best for them.

Suggested Action

💡 Select 'Suggested Script' for an automatically generated script using the guidelines from Microsoft. Note: As of August 2020, for every new Azure SQL database and elastic pool database, the MAXDOP database-scoped configuration will be set to 8 by default.

Review the max degree of parallelism setting.

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

Further Reading

Configure the max degree of parallelism Server Configuration Option | Microsoft Docs