Azure SQL Database – Maxdop Updated

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/

 

 

 

 

 

2 thoughts on “Azure SQL Database – Maxdop Updated

  1. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s