Azure SQL - altering MAX DOP value at Database scope level is not taking place

[amarantha]

I am trying to change the MAX DOP value for my Azure Managed SQL which currently is set to ZERO. Here is the script I am running:

USE [my-sql-db] 
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP =5 ; 

the script works fine and returns success, but then when I run

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

I see the number is set to ZERO

and when I run select * from sys.dm_exec_query_memory_Grants I can see the value is set to 1.

but then when I check the DB settings in SSMS i can see the correct value of MAXDOP

I am wondering why do I get conflicting numbers when I check the DB Settings in SSMS vs when I checksys.configurations or sys.dm_exec_query_memory_Grants ?

[irl]

MAXDOP for a given query execution is negotiated at runtime, taking into account a number of factors.

This article from 2013 gives the nuts and bolts involved at that time.
https://techcommunity.microsoft.com/t5/sql-server-support-blog/how-it-works-maximizing-max-degree-of-parallelism-maxdop/ba-p/317840

Since that article was written, the database scoped configuration setting for MAXDOP was added. It doesn't replace the setting in sys.configurations which can be set via the UI in SSMS or via sp_configure. Rather, it's another layer at which MAXDOP can be influenced for a given query. Setting the database scoped configuration MAXDOP option should not be expected to change the value stored in the instance-wide MAXDOP setting.

Regardless of the MAXDOP candidate determined by evaluating MAXDOP system settings within their hierarchy and applicability to a query execution, there are additional query and runtime considerations that may determine DOP for execution.

Use of a non-inlined scalar user defined function, for example, can result in a serial plan rather than parallel (or at least a serial plan zone).

One of the runtime factors influencing DOP is "Max Workers Count" with current execution context ID 0 workers, current reserved parallel workers, and desired parallel worker reservation in mind. If the desired parallel worker reservation would make that sum exceed "Max Workers Count" the candidate DOP is downgraded until that desired reservation fits within "Max Workers Count".

The allowed memory grant can also reduce candidate DOP for a query. I've seen this most often and maybe exclusively with columnstore bulk inserts - if I want DOP 8 but the optimizer estimates the allowed memory grant for the query execution is too small to perform the bulk insert well, candidate dop will be lowered.

And of course, when selecting a query plan, a serial query plan may be chosen rather than a parallel plan if its cost is lower than parallel plan cost.

But... not if it's cost is too low. There is an instance-wide setting "Cost Threshold for Parallelim". Default CTFP is 5. If the cost of the serial plan is is below CTFP, a parallel plan won't be selected (without the influence of something like the ENABLE_PARALLEL_PLAN_PREFERENCE hint).