MAXDOP and Azure SQL DB

The infamous setting that we all know and love – MAXDOP. Did you know that you can actually control MAXDOP when using Azure SQL Database? You might not be able to tinker with the Cost Threshold for Parallelism setting but you sure can with MAXDOP.

How? Simply via the ALTER DATABASE SCOPED CONFIGURATION command.

In this quick post I will show you my parallel plan and how I use MAXDOP = 1 to suppress parallel plan generation so the operation will be executed serially. (Disclaimer – I am not saying this is the right thing to do, merely using it as an example of tweaking this setting, to be honest in 10 years I have changed MAXDOP = 1 twice). I executed a query in Azure. You can see the classic operators such as gather streams and repartition streams.

GonePara

What is the default MAXDOP setting for Azure SQL Database?

SELECT * FROM sys.database_scoped_configurations

It is Zero.

defaulto

Let’s make the plan a serial one by using the below command when then I run the query straight after.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1

serials

Checking the properties of the SELECT operator you can clearly see the effects of the ALTER command.

dop1

Use it wisely.

2 thoughts on “MAXDOP and Azure SQL DB

  1. Pingback: Dew Drop - September 13, 2018 (#2802) - Morning Dew

  2. Pingback: Forcing MAXDOP In Azure SQL DB – Curated SQL

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