Contents
When to use maxdop to improve database performance?
MAXDOP was not automatically changed for existing databases created prior to September 2020. In general, if the database engine chooses to execute a query using parallelism, execution time is faster. However, excess parallelism can consume additional processor resources without improving query performance.
What should the max degree of parallelism be in SQL Server?
Setting max degree of parallelism (MAXDOP) to 0 allows SQL Server to use all the available processors up to 64 processors. However, this is not the recommended value for most cases. For more information on the recommended values for max degree of parallelism, see the Recommendations section in this page.
What’s the default maxdop for Azure SQL database?
In Azure SQL Database, the default MAXDOP setting for each new single database and elastic pool database is 8. This default prevents unnecessary resource utilization, while still allowing the database engine to execute queries faster using parallel threads.
How to change maxdop in Transact-SQL?
Transact-SQL 1 Open a new query window. 2 Connect to the database where you want to change MAXDOP. You cannot change database scoped configurations in the master database. 3 Copy and paste the following example into the query window and select Execute.
How to configure the max degree of parallelism?
For more on MAXDOP in SQL Server, see Configure the max degree of parallelism Server Configuration Option. MAXDOP controls intra-query parallelism in the database engine.
How does excess parallelism affect SQL Server performance?
At scale, excess parallelism can negatively affect query performance for all queries executing on the same database engine instance. Traditionally, setting an upper bound for parallelism has been a common performance tuning exercise in SQL Server workloads.
How to change maxdop in Azure SQL Server?
In Azure SQL Database, you can change the default MAXDOP value: At the query level, using the MAXDOP query hint. At the database level, using the MAXDOP database scoped configuration. Long-standing SQL Server MAXDOP considerations and recommendations are applicable to Azure SQL Database.