What is the Priority of Database Scoped Configurations? – Interview Question of the Week #254

Question: Which wins when server and database MAXDOP settings differ? A nonzero database-scoped MAXDOP overrides the server setting, unless a query-level override applies. Resource Governor can still cap it.

After my database-scoped configuration article, a regular client asked what a query follows if someone changes both levels. The original example used server MAXDOP 2 and database MAXDOP 4.

For that example, the database permits a higher degree of parallelism than the server’s ordinary default. MAXDOP 0 at database scope is different: it inherits the server setting. A MAXDOP query hint can override that default, while a workload group’s limit may cap the effective value.

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

SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = 'MAXDOP';

SELECT * FROM AdventureWorks2025.Sales.SalesOrderDetail
ORDER BY UnitPrice DESC;

-- Historical illustration only, not run by this diagnostic script:
-- EXEC sys.sp_configure N'max degree of parallelism', 2;
-- RECONFIGURE;
-- USE AdventureWorks2025;
-- ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

Run the two configuration queries in the database whose setting you want to inspect. The read-only SELECT retains the original SalesOrderDetail demonstration, with the installed sample name updated. The commented lines show the historical configuration changes without silently applying them.

The original server-setting snippet omitted RECONFIGURE. A changed configured value is not necessarily the value in use; compare both columns. Changing a shared instance setting just to reproduce an interview answer affects other databases too.

MAXDOP is a limit, not a request to use exactly that many workers for every query. A serial plan is still possible. Don’t infer the complete precedence chain from the number of executions of one operator alone.

I like being able to tune one database without changing every application’s default. But the original slogan that database settings always beat global settings was too broad: this answer describes MAXDOP’s documented rules, not every scoped option.

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