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.
What is the default MAXDOP setting for Azure SQL Database?
SELECT * FROM sys.database_scoped_configurations
It is Zero.
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
Checking the properties of the SELECT operator you can clearly see the effects of the ALTER command.
Use it wisely.