About 1.5 years I wrote about MAXDOP setting within Azure SQL DB, more specifically the fact that the default setting being 0.
(https://blobeater.blog/2018/09/12/maxdop-and-azure-sql-db/)
Now, you should be aware that Microsoft will be changing this over August, they will be changing the default setting from 0 ( meaning unlimited, well more so to the limits of the underlying hardware) to 8 for new databases created. Your current databases will not change.
Why are they doing this? Based on their blog they state to “reduce the frequency and severity of incidents caused by excessive query parallelism, and to improve customer workload performance by reducing unnecessary resource utilization”. Whether this is the right setup for you and your workloads that is down to you to determine, however this is something that I will be monitoring because if I feel that I need to change this myself (which I have done so in the past to MAXDOP = 1 ) then do not forget that you can execute the below scoped to the database:
ALTER
DATABASE
SCOPED CONFIGURATION
SET
MAXDOP = 0
If you feel that you are suffering from MAXDOP set to 0 then you could change this to 8 manually for current databases.
ALTER
DATABASE
SCOPED CONFIGURATION
SET
MAXDOP = 8
Never forget that you have the option of working at query level too (query hint), that is if you require this granular level.
https://blobeater.blog/2019/06/25/azure-sql-db-maxdop-hint/
What if you have configured your instance with only 4 vCores ? Wouldn’t it be better for MS to default MAXDOP to 2. Or if you have only 8 vCores then default MAXDOP to 4. Yes, for an instance over 8 vCores I can understand defaulting MAXDOP to 8.
LikeLike
Yes I see what you mean. Have a read of https://techcommunity.microsoft.com/t5/azure-sql-database/changing-default-maxdop-in-azure-sql-database/ba-p/1538528
They seem to be basing this generically across all their telemetry data. There is a section there “Why did you choose 8 as the new default MAXDOP?”
thanks
LikeLike