Configure the Max Degree of Parallelism (MAXDOP) in Azure SQL Database

This article explores the Maximum Degree Of Parallelism (MAXDOP) for the Azure SQL Database.

Introduction

A DBA needs to do proactive monitoring, troubleshooting, and work on optimizing your query workload. The database performance isn’t straightforward. It combines various factors such as server configuration, database design, database configurations, query, index, statistics. You should be aware of the MAXDOP configuration irrespective of whether you work for on-premise or Azure SQL Databases.

Before you go through this article, I recommend you get a basic understanding using the following articles:

Azure SQL DB runs on Microsoft SQL Server’s latest stable version in a Platform-as-a-Service (PaaS). In the relational SQL Server instance, you might be familiar with the Parallelism concept. Query optimizers improve query performance, reduce execution time by splitting tasks into smaller chunks, running them simultaneously, and combining final output results.

For example, the following figure describes query execution with a single thread and multi-thread execution using Parallelism.

You can specify value for the Max Degree of Parallelism (MAXDOP) in a traditional on-premise SQL Server instance level, database level or query level(query hints).

This article explores the following questions for the Azure SQL Database.

What is the default MAXDOP for Azure SQL DB?
Do we have the flexibility to configure MAXDOP in Azure databases? If yes, how can we configure it?
What is the Cost Threshold of Parallelism for Azure DB?

MAXDOP for Azure SQL Databases

In a traditional SQL Server, the default value for MAXDOP is Zero(unlimited). Azure SQL Db allows configuring MAXDOP for each database and elastic pool. By default, the configured value is 8. To verify this configuration, connect to the Azure database in SSMS and view the database property. In the database scoped configuration, verify the MAXDOP set to 8. The reason behind this value is the telemetry data collection from a variety of customer workloads.

Note: If you have any Azure SQL DB before September 2020, the MAXDOP value was zero. You should consider reviewing it for the new default value 8.

The following table describes the difference in query execution behavior due to different MAXDOP values.

Value	Description
0	The database engine can use any number of parallel threads with the upper cap defined by the total number of logical processors.
1	SQL Server database engine does not execute queries in parallel mode.
>1	The database engines can use the parallel threads defined by the MAXDOP. For example, MAXDOP 8 allows a maximum of 8 parallel threads.

Modifying MAXDOP using database scoped configurations

Azure SQL Database does not allow setting up configurations at an instance level similar to an on-premise SQL Server instance. You can use database scoped configuration for setting up MAXDOP for a specific Azure SQL DB.

To check the currently configured value of MAXDOP, use the sys.database_scoped_configurations system catalog view.

1 2	SELECT [value] as CurrentMAXDOP FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';

Suppose we require MAXDOP 6, you need to run ALTER DATABASE SCOPED CONFIGURATION script as below.

1	ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 6;

Note: The above specified ALTER DATABASE SCOPED CONFIGURATION works with Azure SQL Database without configuring geo-replication, read scale-out replicas enabled, and Hyperscale secondary replicas

Now, we can verify the modified MAXDOP configurations.

Alternatively, open Azure DB properties and view MAXDOP as highlighted below.

You can configure different MAXDOP values for primary and secondary Azure databases. To set the MAXDOP value for the secondary database, use the following queries:

Check the currently configured MAXDOP for secondary

1
2

SELECT [value], value_for_secondary FROM sys.database_scoped_configurations
WHERE [name] = 'MAXDOP';
Use database scoped configurations for secondary

1

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 1;

You can also use Windows PowerShell for executing the alter database command and view the modified MAXDOP configuration.

$dbName = "azuredemodatabase"

$serverName = "azuredemosqldemo.database.windows.net"

$serveradminLogin = "sqladmin"

$serveradminPassword = "TCS@system1"

$desiredMAXDOP = 8

$params = @{

'database' = $dbName

'serverInstance' = $serverName

'username' = $serveradminLogin

'password' = $serveradminPassword

'outputSqlErrors' = $true

'query' = 'ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = ' + $desiredMAXDOP + ';

SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''MAXDOP'';'

}

Invoke-SqlCmd @params

Execute the PowerShell code in Windows PowerShell editor or Visual Studio code. It returns the updated MAXDOP as 8 as shown in this case.

Note: By default, the secondary replicas inherit the MAXDOP value from the primary replica

Specify MAXDOP at the query level

If you specify MAXDOP using the database scoped configuration in Azure SQL Database, it is applicable for all queries. We might have different requirements for a specific query, or we do not want that query to use Parallelism. In this case, we can utilize the query hints in Azure DB similar to the on-prem SQL Server. For example, the below query set MAXDOP to 4 for the specified value.

SELECT [SalesOrderID]

,[OrderQty]

,[UnitPrice]

,[UnitPriceDiscount]

,[LineTotal]

FROM [SalesLT].[SalesOrderDetail]

WHERE SalesLT.SalesOrderDetail.SalesOrderID>71780

OPTION (MAXDOP 4);

Configure Parallel Index Operations

Similar to the T-SQL queries, index statements also utilize MAXDOP value configured at the database level in Azure SQL DB. The index operation such as create an index, alter an index, drop a clustered index might be resource-intensive; therefore, we might require a different MAXDOP in few cases.

The single index statement uses MAXDOP, current workload, and index statistics to determine the number of CPUs. SQL Server might decide to go with lower Parallelism for creating an index on a busy system.

CREATE INDEX IX_Product_Name

ON [SalesLT].[Product] ([Name])

WITH (MAXDOP=4);

Similarly, the following ALTER INDEX statement specifies MAXDOP 6 for the index rebuild operation.

ALTER INDEX ALL ON [SalesLT].[Product]

REBUILD WITH

( MAXDOP = 6

, SORT_IN_TEMPDB = ON

, ONLINE = ON);

MAXDOP and DBCC operations

You can also specify a specific MAXOP for DBCC operations such as DBCC CHECKDB, DBCC CHECKTABLE, and DBCC CHECKFILEGROUP. Usually, DBCC operations are resource-intensive for large databases. Therefore, you can control maximum Parallelism by specifying MAXDOP at the query level.

For example, the below query runs DBCC CHECKDB on Azure SQL Database with MAXDOP 4.

MAXDOP and Cost Threshold of Parallelism

The SQL Server query optimizer uses the Max degree of parallelism and cost threshold of Parallelism for determining whether the query will execute in serial or parallel mode. If the query cost is above the cost threshold of Parallelism, SQL Server runs the query parallelly.

To check the cost threshold for Parallelism configured value, query the sys.configurations system view.

SELECT @@VERSION

SELECT name, value, minimum, maximum, value_in_use , description

FROM sys.configurations

WHERE name = 'cost threshold for parallelism'

You can modify the cost threshold for Parallelism in an on-premise SQL Server instance. However, Azure SQL Db does not allow configuring it. If you try setting cost threshold for Parallelism in Azure SQL Database similar to on-premise SQL instance, it generates the following error.

Microsoft guidelines for MAXDOP

Microsoft documents share the below guidelines for the MAXDOP for both on-premises and Azure databases. In most cases, you should not modify the MAXDOP configuration value, which is optimal based on collected data. For the specific workloads, you can use a better MAXDOP. However, you should carefully modify it to cause unnecessary resource utilization and impact your overall service objective.

Note: You should avoid setting up MAXDOP to zero as it might cause excessive Parallelism

The general guidance for setting up MAXDOP for SQL Server 2016 onwards is as below.

NUMA configuration	Number of processors	Recommendations for MAXDOP
Single NUMA node	<=8 logical processors	Maximum of logical processor count of less than that
	>8 logical processors	8
Multiple NUMA node	<=16	Maximum of logical processor count of less than that
Multiple NUMA node	>16	MAXDOP at ½ of logical processors per NUMA node. Maximum value 16

If you want to check NUMA configurations for Azure SQL Database, you can query sys.dm_os_schedulers. The following table displays NUMA status and online schedulers.

SELECT

CASE COUNT(DISTINCT parent_node_id)

WHEN 1

THEN 'NUMA is disabled'

ELSE 'NUMA is enabled'

END AS NumaChecks

FROM sys.dm_os_schedulers

WHERE

parent_node_id <> 32

As shown below, for Azure SQL Database’s basic service tier lab environment, NUMA is enabled.

Note: The MAXDOP is enforced on the task level. You should not confuse it with the per request or per query. Therefore, in parallel execution, you can see a request with multiple tasks utilizing MAXDOP. You can refer to Microsoft docs Thread and Task Architecture Guide for more details

Conclusion

This article explores MAXDOP configurations for Azure SQL Database. Its default value is 8 for Azure DB. You can modify it using the database scoped configuration. However, you should not change it blindly. Monitor your database behavior, wait statistics, query performance and tune it, if required. You can also use query hints for specific MAXDOP at the query level, create an index, alter the index.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023