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.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 10;

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]
     ,[RevisionNumber]
     ,[OrderDate]
     ,[DueDate]
     ,[ShipDate]
     ,[Status]
     ,[OnlineOrderFlag]
     ,[SalesOrderNumber]
     ,[PurchaseOrderNumber]
     ,[AccountNumber]
     ,[CustomerID]
     ,[ShipToAddressID]
     ,[BillToAddressID]
     ,[ShipMethod]
     ,[CreditCardApprovalCode]
     ,[SubTotal]
     ,[TaxAmt]
     ,[Freight]
     ,[TotalDue]
     ,[Comment]
     ,[rowguid]
     ,[ModifiedDate]
 FROM [SalesLT].[SalesOrderHeader]
 WHERE SalesLT.[SalesOrderHeader].SalesOrderID>71787
 OPTION (MAXDOP 4);

If you want to learn about MAXDOP in detail for SQL Server and Azure SQL Database, refer to the article - https://www.sqlshack.com/configure-the-max-degree-of-parallelism-maxdop-in-azure-sql-database/