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.
Pingback: Dew Drop - September 13, 2018 (#2802) - Morning Dew
Pingback: Forcing MAXDOP In Azure SQL DB – Curated SQL
Hi, thanks for your post!
One quick question. It is not clear to me if I can use the Option(MAXDOP) command on Azure SQL Database. The only way is to alter the database scoped configuration, or if I use the command on a select query it will work as well?
LikeLike
inspired me to write – https://blobeater.blog/2019/06/25/azure-sql-db-maxdop-hint/
LikeLike
Pingback: Azure SQL Database – Maxdop Updated | All About Data