Changing default MAXDOP in Azure SQL Database and Azure SQL Managed Instance

[Dimitri_Furman]

Update: The change of default MAXDOP described in this article will also be applicable to new Azure SQL managed instances. The article has been updated accordingly.

Executive Summary

To improve customer workload performance and reduce unnecessary resource utilization, the default MAXDOP setting for new databases and managed instances in Azure SQL is changing from the previous default of 0 (unlimited) to 8.

Why MAXDOP matters

The “max degree of parallelism” (a.k.a. MAXDOP) configuration option controls the ability of the MSSQL database engine to use parallel threads during query processing. When MAXDOP is other than 1, the database engine may execute queries using multiple concurrent threads. This often results in shorter query duration due to additional CPU resources used for query processing. The number of concurrently executing threads, and the resulting CPU utilization, depends on the MAXDOP value. Higher values generally result in more threads and higher resource utilization.

If a query is using parallelism, it often gets faster as MAXDOP is increased, though after some point additional threads may just use extra CPU cycles without reducing query duration. To learn more about query parallelism, see the Degree of Parallelism section in the Query Processing Architecture Guide.

Long-standing MAXDOP considerations and recommendations are applicable to Azure SQL. This includes single databases, databases in elastic pools, and managed instances. For all of these deployment options, customers can control MAXDOP at the database level using the MAXDOP database-scoped configuration.  For managed instances, customers can also set the server 'max degree of parallelism' configuration option, and can control MAXDOP at the Resource Governor workload group level. For all Azure SQL deployment options, MAXDOP can additionally be controlled at the individual query level by using the OPTION (MAXDOP) query hint, which overrides MAXDOP configurations set in the database or instance scope (though without exceeding the Resource Governor workload group cap, if any).

Changing MAXDOP can have major impact on query performance and resource utilization, both positive and negative. However, there is not a single MAXDOP value that is optimal for all workloads. The recommendations for setting MAXDOP are nuanced, and depend on many factors. Customers wishing to achieve optimal performance for their workloads may need to fine-tune MAXDOP, using published recommendations as the starting point.

Importantly, while increasing MAXDOP often reduces duration for long-running queries, excessive parallelism can cause unnecessary CPU and worker utilization, and make the overall workload performance worse by starving other queries of these resources. In extreme cases, excessive parallelism can consume all database, elastic pool, or managed instance resources, causing query timeouts, errors, and application outages.

Historically, the default MAXDOP value in SQL Server has always been set to 0. This means that query parallelism is limited only by the hardware bounds of the machine running the SQL Server instance (up to the maximum MAXDOP of 64). For backward compatibility reasons, this remains the default value in SQL Server, though starting with SQL Server 2019, it is possible to change the instance level MAXDOP during initial instance setup.

In Azure SQL, the default MAXDOP value has also been set to 0 (unlimited) for every new database and managed instance, for similar reasons. However, over time, and especially as databases, elastic pools, and managed instances with many cores became widely used, performance problems due to excessive parallelism caused by MAXDOP set to 0 became more frequent. This particularly impacts customers who are not familiar with the MAXDOP option, and leave it at the default of 0.

To address these problems, we are making the following changes:

• For every new single database and elastic pool database, the MAXDOP database-scoped configuration will be set to 8 by default.
• For every new managed instance, the 'max degree of parallelism' instance option will be set to 8 by default.

Frequently Asked Questions

Why are you changing default MAXDOP for new Azure SQL databases and managed instances?

This change is to reduce the frequency and severity of incidents caused by excessive query parallelism, and to improve customer workload performance by reducing unnecessary resource utilization. We expect that this change will benefit the overwhelming majority of customers using Azure SQL.

When will this change happen?

For single databases and databases in elastic pools, this change has rolled out in September of 2020. For managed instances, we expect this change to roll out worldwide in May of 2021.

Will this change impact my existing Azure SQL databases and managed instances?

No. Existing databases and instances will not be modified. This change applies only to new databases and managed instances created after this change has been rolled out.

I know that MAXDOP setting different from 8 is optimal for my workload. What should I do?

Customers who have determined that a specific MAXDOP setting is optimal for their workloads, or customers who prefer to keep MAXDOP 0 used previously by default, can add a step to their deployment processes to change MAXDOP after database or instance creation.

Should I change MAXDOP on my existing Azure SQL databases and managed instances?

Customers with existing databases and instances that use the default MAXDOP 0 should consider changing MAXDOP according to standing recommendations. This may improve performance, reduce the risk of performance and availability incidents, and in some cases reduce costs by being able to avoid unnecessary resource utilization, and thus scale down to a lower service objective.

We recommend that customers avoid MAXDOP 0 even if it does not appear to cause problems currently. This will reduce the risk of potential future problems due to excessive parallelism if a database or instance is scaled up to use more cores, or if future hardware generations in Azure SQL provide more cores for the same database service objective.

As with most configuration changes, thorough testing is recommended before making changes in critical environments.

How can I change MAXDOP for an existing database or managed instance?

MAXDOP can be changed using the ALTER DATABASE SCOPED CONFIGURATION statement in the scope of a database. For managed instances, MAXDOP can also be changed for the entire instance using the sp_configure stored procedure, or capped to a maximum value using Resource Governor workload groups.

To execute the necessary T-SQL statement, customers can use any client tool or programming language that can connect to the database or instance and execute T-SQL commands. The following client tools are commonly used:

1. Query editor, which is a part of Azure portal.
2. SQL Server Management Studio.
3. Azure Data Studio.

For example, to change MAXDOP to 4 for a database, use the following T-SQL command when connected to the target database:

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

As another example, to change MAXDOP to 1 for a managed instance, use the following T-SQL commands when connected to the target managed instance:

EXEC sp_configure 'max degree of parallelism', 1;
RECONFIGURE;

How do I know if a change to MAXDOP made a difference for my workload?

Customers can see the impact of a change in MAXDOP by making sure that Query Store is enabled for a representative period of time before and after the change is made, to capture resource utilization and performance statistics for each query. For more details, see A/B testing.

What are the symptoms of excessive query parallelism in Azure SQL databases and managed instances?

In Azure SQL, one common symptom of excessive parallelism is exceeding the resource governance limits on the number of worker threads. When this happens, error 10928, “Resource ID : 1. The request limit for the database is N and has been reached” is raised, where N stands for the worker thread limit for the database, elastic pool, or managed instance (note, however, that there are other possible causes for this error).

CXPACKET waits may be another symptom of excessive parallelism. Some amount of CXPACKET waits is normal whenever query parallelism is used; however, if CXPACKET waits dominate other waits types while CPU utilization is greater than 80-90%, it likely also indicates excessive parallelism.

Why did you choose 8 as the new default MAXDOP?

Our telemetry data and our experience running the Azure SQL service show that MAXDOP 8 is the optimal value for the widest variety of customer workloads. It is a safe option that reduces the likelihood of performance problems due to excessive parallelism, while still allowing queries to execute faster by using more threads.

At the same time, workloads where a different MAXDOP value is optimal do exist. Customers can experiment with different MAXDOP settings to determine what works best for them.

Shouldn’t the database engine automatically choose the optimal degree of parallelism for every query it executes?

Improvements in the MSSQL database engine are made continuously. Automatic Tuning, and the Intelligent Query Processing family of features are two examples of improvements in query processing and query performance made in recent years. Today, the database engine already reduces query parallelism at query startup time in response to a shortage of available worker threads. Query processing improvements in future releases of the database engine may adjust parallelism dynamically in a broader set of scenarios.

Why are you changing the default MAXDOP regardless of the database or instance service objective? Shouldn’t this only apply to service objectives with more than 8 cores?

Limiting MAXDOP to 8 is safe for databases and instances with 8 or less cores (or scheduler threads). In this case, parallelism will be limited by the smaller number of available cores, rather than by the MAXDOP setting. Limiting MAXDOP to 8 protects smaller databases and instances from being exposed to excessive parallelism if scaled up to use more than 8 cores, or if future hardware generations in Azure SQL provide more cores for the same database service objective.

What is the impact of this change on index rebuild?

By default, indexes are rebuilt with parallelism, using the instance or database-scoped MAXDOP setting. On databases and instances with more than 8 cores, this change may cause index rebuilds to take longer due to lower degree of parallelism used. If this happens, you can increase parallelism for each index rebuild operation by using the MAXDOP option in the ALTER INDEX … REBUILD statement.

What happens if I restore a database that has a non-default database-scoped MAXDOP configuration on a managed instance?

No changes are made to database-scoped configurations as part of database restore on a managed instance. The database-scoped MAXDOP configuration in effect when the database was backed up will remain in effect for the database when it is restored.

How can I ask questions or provide feedback on this change?

Customers can ask questions or provide feedback via comments to this blog post, or via email to IntelligentQP [at] microsoft.com.

[Johannes_Vink]

And to query the current MAXDOP setting:

SELECT name, value_in_use FROM sys.configurations WHERE description LIKE '%parallelism%';
-- Not good on PaaS!

However... that raises also the next question: when will the cost threshold for parallelism be possible to be modified? Because THAT will impact the parallelism a lot.

https://www.brentozar.com/archive/2017/03/why-cost-threshold-for-parallelism-shouldnt-be-set-to-5/

[Dimitri_Furman]

@Johannes_Vink Thanks for the comment. The query you posted will work in SQL Server and in Azure SQL Managed Instance, however it will always return 0 in Azure SQL Database. In the latter case, the database-scoped configuration is what controls MAXDOP, so the query should be:

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

We will investigate the possibility of letting customers control the cost threshold for parallelism in Azure SQL Database. However, our preferred long-term solution would be to improve the optimizer so that parallelism is used optimally in all cases without requiring configuration changes by users.

[zaynulabadintuhin]

I' using azure hyperscale version 6core . So my question is how many maxdop should i set

[Dimitri_Furman]

@zaynulabadintuhin - The recommendations for setting MAXDOP in Hyperscale are the same as for any other Azure SQL service tier. For most Azure SQL workloads, we recommend leaving MAXDOP set to the default value for new databases, which is 8. If you determined empirically that a different value works better for your workload, you can certainly use it. If your workload profile changes later, you can change MAXDOP as well, if that makes performance better.

[zaynulabadintuhin]

thanks for the reply and I'm using 8. But I have faced a strange scenario I going to describe it below.

While calling multiple Stored procedures from the .net framework API asynchronously, it is taking long time for the azure sql  to return data. But while calling the same stored procedures individually or one by one from API, azure sql  is returning data significantly faster.

To optimize the performance I have already enabled SET NOCOUNT ON, tried local variable to avoid parameter sniffing and also tried SET ARITHABORT ON, but eventually having the same outcome. I am using angularjs in frontend, .net framework for the api.

NOTE:

• There are 4 different SPs, which are not dependent on each other.

• All these SPs are doing read operations only, no write or update operation.

While calling the SPs asynchronously, they should be executed and return corresponding data independently. but that is not happening. When I am calling 1 sp it takes 3-4 second, while calling 2 it becomes 8 second, while calling 4, then it becomes 16-18 second for the overall process, even though the calls are asynchronous

I have provided 2 SPs along with API and repo

API code:

public async Task<HttpResponseMessage> GetDataAPI(string param1, string param2, string param3, string param4, string param5, bool? Param6, bool? Param7, string param8, string param9, bool? param10, bool? param11, string param12, int? param13)
{
    Model model = await _repo.GetData(param1, param2, param3, param4, param3, param4, param5, param6, param7, param8, param9, param10, param11);
    return Request.CreateResponse(HttpStatusCode.OK, overview);
}

Repo code:

public async Task<model> GetData(string param1, string param2, string param3, string param4, string param5, bool? Param6, bool? Param7, string param8, string param9, bool? param10, bool? param11, string param12, int? param13)
{
    var _param1 = string.IsNullOrEmpty(param1) ? new SqlParameter("@param1", DBNull.Value) : new SqlParameter("@param1", param1);
    var _param2 = string.IsNullOrEmpty(param2) ? new SqlParameter("@param2", DBNull.Value) : new SqlParameter("@param2", param2);
    var _param3 = string.IsNullOrEmpty(param3) ? new SqlParameter("@param3", DBNull.Value) : new SqlParameter("@param3", param3);
    var _param4 = string.IsNullOrEmpty(param4) ? new SqlParameter("@param4", DBNull.Value) : new SqlParameter("@param4", param4);
    var _param5 = string.IsNullOrEmpty(param5) ? new SqlParameter("@param5", DBNull.Value) : new SqlParameter("@param5", param5);
    var _param6 = !param6.HasValue ? new SqlParameter("@param6", DBNull.Value) : new SqlParameter("@param6", param6);
    var _param7 = !param7.HasValue ? new SqlParameter("@param7", DBNull.Value) : new SqlParameter("@param7", param7);
    var _param8 = string.IsNullOrEmpty(param8) ? new SqlParameter("@param8", DBNull.Value) : new SqlParameter("@param8", param8);
    var _param9 = string.IsNullOrEmpty(param9) ? new SqlParameter("@param9", DBNull.Value) : new SqlParameter("@param9", param9);
    var _param10 = !param10.HasValue ? new SqlParameter("@param10", DBNull.Value) : new SqlParameter("@param10", param10);
    var _param11 = !param11.HasValue ? new SqlParameter("@param11", DBNull.Value) : new SqlParameter("@param11", param11);

    return await _databaseContext.Database.SqlQuery<model>("exec sp1 @param1, @ param2, @ param3, @ param4, @ param5, @ param6, @ param7, @ param8,@ param9,@ param10,@ param11", _param1, _param2, _param3, _param4, _param5, _param6, _param7, _param8, _param9, _param10, _param11).FirstOrDefaultAsync();
}

Here is one of the SPs:

ALTER PROCEDURE [dbo].[sp1]            
@param1 bit=NULL,                                               
@param2 nvarchar(100)=NULL,                                               
@param3 bit=NULL,                                                  
@param4 nvarchar(100)=NULL,                                
@param5 bit = Null,                                            
@param6 nvarchar(100)=NULL,                         
@param7 nvarchar(50) = Null,                          
@param8 nvarchar(100) = Null,                          
@param9 bit = Null,                
@param10 nvarchar(30) = Null,                
@param11 int = Null                  
                  
AS                                      
BEGIN                                      
 SET NOCOUNT ON;          
 --Set arithabort on;    
                   
      declare @parameter1 int , @parameter2 int, @parameter3 int                      
                      
                        
   select @parameter1 = count(distinct a.col1)                            
   from table1 a                                     
                                 
    where        
 ((col2) in (SELECT * FROM string_split((@param4), ',')))         
 and (@param1 is null or @param1= a.col12)                                          
    and (@param3 is null or  @param3=a.col16)                                      
    and (@param6 is null or @param6=a.col18)                                          
    and (@param2 is null or @param2=a.col17)                                          
    and (@param5 is null or @param5=a.col3)                                      
        AND (@param7 is null or @param7 = a.col7)                          
    AND (@param8 is null or @param8 = a.col19)                          
                             
    AND (@param9 is null or @param9 = a.col21)                          
    and (@param10 is null or @param10 = a.col25)                
 and (@param11 is null or @param11 = a.col26)                 
                           
    select                         
   @parameter2 = ISNULL(SUM(a.col13),0),                             
   @parameter3 = ISNULL(SUM(a.col8),0)                               
    from table2 a                                                
   where ((col2) in (SELECT * FROM string_split((@param4), ','))) and                
   (@param2 is null or @param2 = a.col12) and                  
   (@param6 is NULL or @param6=a.col9) AND                               
  (@param7 is null or @param7=a.col16) and                              
   (@param8 is null or @param8=a.col11)  and                            
   (@param1 is null or @param1 = a.col3) and                            
   (@param5 is null or @param5 = a.col6)  and    
   (@param3 is null or @param3 = a.col4) and                  
   (@param9 is null or @param9 = a.col5)                  
    and (@param10 is null or @taxId = a.col14)                
 and (@param11 is null or @grouper = a.col17)                 
                         
  SELECT @parameter1 AS column1, @parameter2 AS column2, @parameter3 as column3                      
                                      
END

I have used option(recompile) as well in SP. Do you have any suggestion why resource are being shared when going to make async call to the SP

@Dimitri_Furman

[Dimitri_Furman]

@zaynulabadintuhin - there could be multiple reasons for what you are observing, likely unrelated to MAXDOP. Troubleshooting this via blog comments isn't the best way to solve the problem, so I suggest you post this in one of MSSQL community forums, such as azure-sql-database on Microsoft Q&A, or on Stack Overflow tagged under azure-sql-database.

[SowmyaG99]

@Dimitri_Furman 

We configured DB with elastic businesscritical  and for now there is only single DB in it. What value should we use over MAXDOP.

Here we are facing issue that some of the queries running for a long time where in onprem they are getting completed with in 20- 30mins but in azure sql db it is taking around 16-18 hours to complete the query.

There is no spikes in dashboard and gone through Query Store as well but no use it is happening in our prod server. Even MS support engineer also not able to find the reason behind it.

Please share your views how it can be going solve. in query wait statics it is showing the wait type as parallelism is high.

Thank you.

Sowmya. 

[Dimitri_Furman]

@SowmyaG99 It is quite unlikely that this level of performance difference is because of parallelism. The most likely causes are either a different query plan used in your on-prem deployment vs. Azure SQL deployment, or a difference in allocated resources, or both. Please continue working with the Microsoft support engineer until the root cause is found.

As far as MAXDOP, the usual recommendation applies - start with the default (8), and then adjust down in decrements of 2 until your overall performance is optimal. But again, this is unlikely to resolve the issue you described.