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/