Javascript required
Skip to content Skip to sidebar Skip to footer
Home / Azure Sql Database Max Pool Size

Azure Sql Database Max Pool Size

This article explores the Maximum Degree Of Parallelism (MAXDOP) for the Azure SQL Database.

Introduction

A DBA needs to do proactive monitoring, troubleshooting, and work on optimizing your query workload. The database performance isn't straightforward. It combines various factors such as server configuration, database design, database configurations, query, index, statistics. You should be aware of the MAXDOP configuration irrespective of whether you work for on-premise or Azure SQL Databases.

Before you go through this article, I recommend you get a basic understanding using the following articles:

  • SQL Server Max Degree of Parallelism Importance
  • Set the Max Degree of Parallelism in SQL Server using different ways

Azure SQL DB runs on Microsoft SQL Server's latest stable version in a Platform-as-a-Service (PaaS). In the relational SQL Server instance, you might be familiar with the Parallelism concept. Query optimizers improve query performance, reduce execution time by splitting tasks into smaller chunks, running them simultaneously, and combining final output results.

For example, the following figure describes query execution with a single thread and multi-thread execution using Parallelism.

Thread distribution with SQL Server Degree Parallelism

You can specify value for the Max Degree of Parallelism (MAXDOP) in a traditional on-premise SQL Server instance level, database level or query level(query hints).

This article explores the following questions for the Azure SQL Database.

  • What is the default MAXDOP for Azure SQL DB?
  • Do we have the flexibility to configure MAXDOP in Azure databases? If yes, how can we configure it?
  • What is the Cost Threshold of Parallelism for Azure DB?

MAXDOP for Azure SQL Databases

In a traditional SQL Server, the default value for MAXDOP is Zero(unlimited). Azure SQL Db allows configuring MAXDOP for each database and elastic pool. By default, the configured value is 8. To verify this configuration, connect to the Azure database in SSMS and view the database property. In the database scoped configuration, verify the MAXDOP set to 8. The reason behind this value is the telemetry data collection from a variety of customer workloads.

Note: If you have any Azure SQL DB before September 2020, the MAXDOP value was zero. You should consider reviewing it for the new default value 8.

MAXDOP configuration for Azure DB

The following table describes the difference in query execution behavior due to different MAXDOP values.

Value

Description

0

The database engine can use any number of parallel threads with the upper cap defined by the total number of logical processors.

1

SQL Server database engine does not execute queries in parallel mode.

>1

The database engines can use the parallel threads defined by the MAXDOP. For example, MAXDOP 8 allows a maximum of 8 parallel threads.

Modifying MAXDOP using database scoped configurations

Azure SQL Database does not allow setting up configurations at an instance level similar to an on-premise SQL Server instance. You can use database scoped configuration for setting up MAXDOP for a specific Azure SQL DB.

To check the currently configured value of MAXDOP, use the sys.database_scoped_configurations system catalog view.

SELECT [ value ] as CurrentMAXDOP FROM sys . database_scoped_configurations

WHERE [ name ] = 'MAXDOP' ;

Check current configuration

Suppose we require MAXDOP 6, you need to run ALTER DATABASE SCOPED CONFIGURATION script as below.

ALTERDATABASESCOPEDCONFIGURATIONSETMAXDOP=6 ;

Specify MAXDOP for specific DB

  • Note: The above specified ALTER DATABASE SCOPED CONFIGURATION works with Azure SQL Database without configuring geo-replication, read scale-out replicas enabled, and Hyperscale secondary replicas

Now, we can verify the modified MAXDOP configurations.

View modified configuration

Alternatively, open Azure DB properties and view MAXDOP as highlighted below.

View MAXDOP in SSMS

You can configure different MAXDOP values for primary and secondary Azure databases. To set the MAXDOP value for the secondary database, use the following queries:

  • Check the currently configured MAXDOP for secondary

    SELECT[ value ] ,value_for_secondaryFROMsys . database_scoped_configurations

    WHERE[ name ]='MAXDOP' ;

  • Use database scoped configurations for secondary

    ALTERDATABASESCOPEDCONFIGURATIONFORSECONDARYSETMAXDOP=1 ;

You can also use Windows PowerShell for executing the alter database command and view the modified MAXDOP configuration.

$ dbName = "azuredemodatabase"

$ serverName = "azuredemosqldemo.database.windows.net"

$ serveradminLogin = "sqladmin"

$ serveradminPassword = "TCS@system1"

$ desiredMAXDOP = 8

$ params = @ {

'database' = $ dbName

'serverInstance' = $ serverName

'username' = $ serveradminLogin

'password' = $ serveradminPassword

'outputSqlErrors' = $ true

'query' = 'ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = ' + $ desiredMAXDOP + ';

        SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ' 'MAXDOP' ';'

}

Invoke - SqlCmd @ params

Execute the PowerShell code in Windows PowerShell editor or Visual Studio code. It returns the updated MAXDOP as 8 as shown in this case.

Update database scoped configuration in PowerShell

  • Note: By default, the secondary replicas inherit the MAXDOP value from the primary replica

Specify MAXDOP at the query level

If you specify MAXDOP using the database scoped configuration in Azure SQL Database, it is applicable for all queries. We might have different requirements for a specific query, or we do not want that query to use Parallelism. In this case, we can utilize the query hints in Azure DB similar to the on-prem SQL Server. For example, the below query set MAXDOP to 4 for the specified value.

SELECT [ SalesOrderID ]

, [ OrderQty ]

, [ UnitPrice ]

, [ UnitPriceDiscount ]

, [ LineTotal ]

FROM [ SalesLT ] . [ SalesOrderDetail ]

WHERE SalesLT . SalesOrderDetail . SalesOrderID > 71780

OPTION ( MAXDOP 4 ) ;

Configure Parallel Index Operations

Similar to the T-SQL queries, index statements also utilize MAXDOP value configured at the database level in Azure SQL DB. The index operation such as create an index, alter an index, drop a clustered index might be resource-intensive; therefore, we might require a different MAXDOP in few cases.

The single index statement uses MAXDOP, current workload, and index statistics to determine the number of CPUs. SQL Server might decide to go with lower Parallelism for creating an index on a busy system.

CREATE INDEX IX_Product_Name

ON [ SalesLT ] . [ Product ] ( [ Name ] )

WITH ( MAXDOP = 4 ) ;

GO

Similarly, the following ALTER INDEX statement specifies MAXDOP 6 for the index rebuild operation.

ALTER INDEX ALL ON [ SalesLT ] . [ Product ]

REBUILD WITH

( MAXDOP = 6

, SORT_IN_TEMPDB = ON

, ONLINE = ON ) ;

MAXDOP and DBCC operations

You can also specify a specific MAXOP for DBCC operations such as DBCC CHECKDB, DBCC CHECKTABLE, and DBCC CHECKFILEGROUP. Usually, DBCC operations are resource-intensive for large databases. Therefore, you can control maximum Parallelism by specifying MAXDOP at the query level.

For example, the below query runs DBCC CHECKDB on Azure SQL Database with MAXDOP 4.

DBCC CHECKDB with MAXDOP

MAXDOP and Cost Threshold of Parallelism

The SQL Server query optimizer uses the Max degree of parallelism and cost threshold of Parallelism for determining whether the query will execute in serial or parallel mode. If the query cost is above the cost threshold of Parallelism, SQL Server runs the query parallelly.

To check the cost threshold for Parallelism configured value, query the sys.configurations system view.

SELECT @ @ VERSION

Go

SELECT name , value , minimum , maximum , value_in_use , description

FROM sys . configurations

WHERE name = 'cost threshold for parallelism'

MAXDOP and Cost Threshold of Parallelism

You can modify the cost threshold for Parallelism in an on-premise SQL Server instance. However, Azure SQL Db does not allow configuring it. If you try setting cost threshold for Parallelism in Azure SQL Database similar to on-premise SQL instance, it generates the following error.

Modify COTFP for Azure

Microsoft guidelines for MAXDOP

Microsoft documents share the below guidelines for the MAXDOP for both on-premises and Azure databases. In most cases, you should not modify the MAXDOP configuration value, which is optimal based on collected data. For the specific workloads, you can use a better MAXDOP. However, you should carefully modify it to cause unnecessary resource utilization and impact your overall service objective.

  • Note: You should avoid setting up MAXDOP to zero as it might cause excessive Parallelism

The general guidance for setting up MAXDOP for SQL Server 2016 onwards is as below.

NUMA configuration

Number of processors

Recommendations for MAXDOP

Single NUMA node

<=8 logical processors

Maximum of logical processor count of less than that

>8 logical processors

8

Multiple NUMA node

<=16

Maximum of logical processor count of less than that

Multiple NUMA node

>16

MAXDOP at ½ of logical processors per NUMA node. Maximum value 16

If you want to check NUMA configurations for Azure SQL Database, you can query sys.dm_os_schedulers. The following table displays NUMA status and online schedulers.

SELECT

CASE COUNT ( DISTINCT parent_node_id )

WHEN 1

THEN 'NUMA is disabled'

ELSE 'NUMA is enabled'

END AS NumaChecks

FROM sys . dm_os_schedulers

WHERE

parent_node_id <> 32

As shown below, for Azure SQL Database's basic service tier lab environment, NUMA is enabled.

Check NUMA status for Azure server

  • Note: The MAXDOP is enforced on the task level. You should not confuse it with the per request or per query. Therefore, in parallel execution, you can see a request with multiple tasks utilizing MAXDOP. You can refer to Microsoft docs Thread and Task Architecture Guide for more details

Conclusion

This article explores MAXDOP configurations for Azure SQL Database. Its default value is 8 for Azure DB. You can modify it using the database scoped configuration. However, you should not change it blindly. Monitor your database behavior, wait statistics, query performance and tune it, if required. You can also use query hints for specific MAXDOP at the query level, create an index, alter the index.

  • Author
  • Recent Posts

Rajendra Gupta

Azure Sql Database Max Pool Size

Source: https://www.sqlshack.com/configure-the-max-degree-of-parallelism-maxdop-in-azure-sql-database/