How To Change Azure SQL DB MAXDOP Settings Using T-SQL Command

In this article, we are going to discuss how to change Azure SQL db MAXDOP settings. Since you have landed on this page, we expect you to have some background information on the ‘max degree of parallelism‘ a.k.a MAXDOP option. For those who don’t, I will give a simple explanation.

Most modern processors come with multi-core NUMA nodes offering SMP (symmetric multi-processing) for running workloads. Since SQL Server is multiprocessor aware, it takes full advantages of executing queries in parallel to achieve maximum performance.

However, going with query parallelism does not necessarily translate to higher performance. Even worse, it might degrade query performance. See the image below:

In a multi-threaded query, also if one query is waiting on a resource, the entire query will have to wait. In some instances, we have seen query execution taking hours, or even days to execute or waiting on a resource. Typically when you run a DMV (Dynamic Management Views), you will be able to see CXPACKET wait types. Try it out yourselves using the following command:

select runnable_tasks_count,work_queue_count,pending_disk_io_count, * from sys.dm_os_schedulers

select * from sys.dm_os_workers
 select * from sys.dm_os_threads

select * from sys.dm_os_tasks
 where session_id > 50

task_state = 'SUSPENDED' or task_state = 'PENDING'
 AND
 select * from sys.dm_os_waiting_tasks where session_id > 50

select * from sys.dm_exec_requests
 where session_id > 50

select * from sys.dm_os_memory_objects

SELECT MO.memory_object_address, MO.type, MO.pages_allocated_count, page_size_in_bytes
 FROM sys.dm_os_memory_objects MO
 inner join sys.dm_os_workers OW on
 MO.memory_object_address = OW.memory_object_address
 inner join sys.dm_os_tasks OT
 on OW.worker_address = OT.worker_address
 inner join sys.dm_exec_requests ER
 on OT.task_address = ER.task_address
 where ER.session_id > 50

The default value for SQL Server is ‘0’, which means SQL Server will prioritize parallelism over serialization. A lot of applications performs better with the MAXDOP setting set to ‘1’. Several of Microsoft’s own applications like Dynamics CRM, Sharepoint 2016, 2019, etc. comes with this recommendation.

Change Azure SQL DB MAXDOP Settings:

Changing the MAXDOP setting on SQL Server on-premises is simple. You can use either the GUI or T-SQL.

USE AdventureWorks2016;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO

However, when we talk about Azure SQL, things become a little more complicated. Initially, Azure SQL used to have ‘cost threshold for parallelism’ set a ‘5’. Issuing a T-SQL command will not allow you to change this. With the release of SQL Server 2016, Microsoft introduced the “Database scoped parameters” and thereon allowed users to change MAXDOP settings.

You can issue the following command to change the value, see below:

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;

Changing the ‘cost threshold for parallelism’ is still not allowed, but I guess we can live with that. In the current version of Azure SQL, the default value is set to ‘0’. You can use the following command to check.

SELECT * FROM sys.database_scoped_configurations

Conclusion

We hope this article was helpful. Your comments and suggestions are welcome in the comments sections below. Thanks for a visit!

Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.