SQL SERVER – 3 Different Ways to Set MAXDOP

There are 3 different MAXDOP scopes: instance, database and statement. Readers of my 16-versus-1 demonstration asked where to configure it.

-- Read-only settings for the current instance and database.
SELECT value, value_in_use FROM sys.configurations
WHERE name = N'max degree of parallelism';
SELECT name, value FROM sys.database_scoped_configurations WHERE name = N'MAXDOP';
-- Instance change, reviewed by an administrator:
-- EXEC sys.sp_configure N'show advanced options', 1;
-- RECONFIGURE;
-- EXEC sys.sp_configure N'max degree of parallelism', 2;
-- RECONFIGURE;
-- Restore the original show advanced options setting if changed.
-- Database change, SQL Server 2016+ in the intended database:
-- ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;
SELECT name FROM sys.databases OPTION (MAXDOP 2);

My original instance example omitted RECONFIGURE and hidden advanced options. Its configuration commands now appear as administrative templates. Database-scoped MAXDOP starts with SQL Server 2016. Zero inherits the instance setting.

A query hint provides a statement-level limit subject to other controls. Resource Governor can further restrict it. MAXDOP does not cap every worker belonging to a request. A serial plan can remain appropriate with a higher setting.

The example value 2 is illustrative. Choose a baseline for CPU topology and workload, then compare duration, CPU, reads and concurrency. Increasing the number does not guarantee speed. Retain previous values for an approved reversal.

Reference: MAXDOP configuration and precedence.

Related reading

• SQL SERVER – 16 CPU vs 1 CPU: Performance Comparison
• Comprehensive Database Performance Health Check
• Pinal Dave on YouTube

MAXDOP is not a promised processor count, it is a parallelism control to evaluate within the workload.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.