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/
Comments