Max Degree of Parallelism Not Optimal

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.

Suggested Action

šŸ’” Aireforge Advisor can automatically generate a script using the guidelines from Microsoft.

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

What Is the Default MAXDOP for Azure SQL Database?

šŸ’” 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.

Microsoft state "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."

Further Reading

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