Configure Max Degree of Parallelism (MAXDOP) for Azure SQL Database

Many factors contribute to the performance of a database, such as server configuration, database design, database configurations, query, index, and statistics. A database administrator (DBA) needs to monitor, troubleshoot, and optimize your query workload. And you should be aware of the MAXDOP configuration regardless of whether you're working for an on-premise or Azure SQL Database.

Azure SQL Db has a default MAXDOP configured value 8 for each Azure SQL database and elastic pool. If you want to modify MAXDOP for Azure, use the database scoped configuration.

The following T-SQL statement configured MAXDOP to 10 for Azure DB.


Once the script executed for Azure DB, you can verify the configured value from the sys.database_scoped_configurations, as shown below.

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

Note: Do not play with MAXDOP in production instances or Azure databases unless you know its implications and it is tested in the non-prod systems.

You can override the default MAXDOP at the query level. For example, we used a different MAXDOP 4 for the select statement. It overrides the default configured we did earlier for the specific query.

SELECT [SalesOrderID]
 FROM [SalesLT].[SalesOrderHeader]
 WHERE SalesLT.[SalesOrderHeader].SalesOrderID>71787

If you want to learn about MAXDOP in detail for SQL Server and Azure SQL Database, refer to the article -

48 views0 comments