Configurer le degré maximal de parallélisme (MAXDOP) dans Azure SQL Database

S’applique à : Base de données Azure SQL Base de données SQL dans Fabric

Cet article décrit le paramètre de configuration degré maximal de parallélisme (MAXDOP) dans Azure SQL Database.

Vue d’ensemble

MAXDOP contrôle le parallélisme intra-requête dans le moteur de base de données. Des valeurs MAXDOP plus élevées produisent généralement plus de threads parallèles par requête et une exécution de requête plus rapide.

Dans Azure SQL Database, le paramètre MAXDOP par défaut pour chaque nouvelle base de données et base de données de pool élastique est 8. Cette valeur par défaut empêche toute utilisation inutile des ressources, tout en permettant au moteur de base de données d’exécuter des requêtes plus rapidement à l’aide de threads parallèles. Il n’est généralement pas nécessaire de configurer plus précisément les charges de travail MAXDOP dans Azure SQL Database, mais cela peut fournir des avantages en tant qu’exercice de réglage des performances avancé.

En général, si le moteur de base de données choisit d’exécuter une requête à l’aide du parallélisme, le temps d’exécution est plus rapide. Toutefois, le parallélisme excessif peut consommer des ressources de processeur supplémentaires sans améliorer les performances de requête. À grande échelle, un parallélisme excessif peut affecter négativement les performances des requêtes pour toutes les requêtes s’exécutant sur la même instance de moteur de base de données. Traditionnellement, la définition d’une limite supérieure pour le parallélisme est un exercice courant de réglage des performances dans les charges de travail SQL Server.

Le tableau suivant décrit le comportement du moteur de base de données lors de l’exécution de requêtes avec différentes valeurs MAXDOP :

Considérations

• Dans Azure SQL Database, vous pouvez modifier la valeur MAXDOP par défaut :

  • Au niveau de la requête, à l’aide de l’indicateur de requête MAXDOP.
  • Au niveau de la base de données, avec la configuration limitée à la base de données MAXDOP.

• Les considérations et recommandations à long terme MAXDOP SQL Server s’appliquent à Azure SQL Database.

• Les opérations d'index destinées à créer ou à recréer un index, voire à supprimer un index cluster, peuvent nécessiter une quantité importante de ressources. Vous pouvez remplacer la valeur MAXDOP de la base de données pour les opérations d’index en spécifiant l’option d’index MAXDOP dans l’instruction CREATE INDEX ou ALTER INDEX. La valeur de MAXDOP est appliquée à l'instruction au moment de son exécution et n'est pas stockée dans les métadonnées de l'index. Pour plus d’informations, consultez Configurer des opérations d’index parallèles.

• En plus des requêtes et des opérations d’index, cette option de configuration au niveau de la base de données pour MAXDOP contrôle également le parallélisme des autres déclarations qui peuvent utiliser l’exécution parallèle, comme DBCC CHECKTABLE, DBCC CHECKDB et DBCC CHECKFILEGROUP.

Recommandations

La modification de MAXDOP pour la base de données peut avoir un impact majeur sur les performances de requête et l’utilisation des ressources, qu’il soit positif ou négatif. Toutefois, il n’existe pas de valeur MAXDOP unique optimale pour toutes les charges de travail. Les recommandations relatives à la définition de MAXDOP sont nuancées et dépendent de nombreux facteurs.

Certaines charges de travail simultanées peuvent mieux fonctionner avec un MAXDOP différent de celui des autres. Un MAXDOP correctement configuré devrait réduire le risque d’incidents liés aux performances et à la disponibilité et, dans certains cas, réduire les coûts en empêchant toute utilisation inutile des ressources et en réduisant ainsi la taille de l’objectif de service.

Parallélisme excessif

Un MAXDOP plus élevé réduit souvent la durée des requêtes gourmandes en ressources processeur. Toutefois, un parallélisme excessif peut aggraver les performances d’autres charges de travail simultanées en privant les autres requêtes de ressources du processeur et du thread de travail. Dans les cas extrêmes, le parallélisme excessif peut consommer toutes les ressources de la base de données ou du pool élastique, ce qui entraîne des délais d’expiration des requêtes, des erreurs et des interruptions de l’application.

Un parallélisme excessif devient problématique lorsque le processeur et les threads de travail reçoivent plus de requêtes simultanées que ce qui peut être pris en charge par l’objectif de service. Évitez le MAXDOP 0 pour réduire le risque de problèmes futurs potentiels dus à un parallélisme excessif si une base de données est montée en puissance, ou si les configurations matérielles futures dans Azure SQL Database fournissent plus de cœurs pour le même objectif de service de base de données.

Modification de MAXDOP

Si vous déterminez qu’un autre paramètre MAXDOP que la valeur par défaut est optimal pour votre charge de travail Azure SQL Database, vous pouvez utiliser l’ALTER DATABASE SCOPED CONFIGURATION instruction T-SQL. Pour obtenir des exemples, consultez la section Exemples avec Transact-SQL ci-dessous. Pour remplacer MAXDOP par une valeur non définie par défaut pour chaque nouvelle base de données que vous créez, ajoutez cette étape à votre processus de déploiement de base de données.

Si le paramètre MAXDOP non défini par défaut n’offre qu’un petit sous-ensemble de requêtes dans la charge de travail, vous pouvez remplacer MAXDOP au niveau de la requête en ajoutant l’indicateur OPTION (MAXDOP). Pour obtenir des exemples, consultez la section Exemples avec Transact-SQL ci-dessous.

Testez minutieusement vos modifications de configuration MAXDOP avec les tests de charge impliquant des charges de requêtes simultanées réalistes.

MAXDOP pour les réplicas principaux et secondaires peut être configuré indépendamment si des paramètres MAXDOP différents sont optimaux pour les charges de travail en lecture-écriture et en lecture seule. Cela s’applique à l’échelle lecture Azure SQL Database, à la géoréplication et aux réplicas secondaires Hyperscale. Par défaut, tous les réplicas secondaires héritent de la configuration MAXDOP du réplica principal.

Sécurité

Autorisations

L'instruction ALTER DATABASE SCOPED CONFIGURATION doit être exécutée en tant qu’administrateur de serveur, en tant que membre du rôle de base de données db_owner ou en tant qu'utilisateur qui a reçu l'autorisation ALTER ANY DATABASE SCOPED CONFIGURATION.

Exemples

Ces exemples utilisent la base de données la plus récente de l'échantillon AdventureWorksLT lorsque l'option SAMPLE est choisie pour une nouvelle base de données unique d'Azure SQL Database.

PowerShell

Configuration MAXDOP au niveau de la base de données

Cet exemple montre comment utiliser l’instruction ALTER DATABASE SCOPED CONFIGURATION pour définir la configuration MAXDOP sur 2. Le paramètre prend effet immédiatement pour les nouvelles requêtes. L’applet de commande PowerShell Invoke-SqlCmd exécute les requêtes T-SQL à définir et retourne la configuration au niveau de la base de données MAXDOP.

$dbName = "sample" 
$serverName = <server name here>
$serveradminLogin = <login here>
$serveradminPassword = <password here>
$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

Cet exemple est destiné à être utilisé avec les bases de données Azure SQL Database avec des réplicas en échelle lecture activés, la géoréplicationet les réplicas secondaires Hyperscale Azure SQL Database. Par exemple, le réplica principal est défini sur un autre MAXDOP par défaut que le réplica secondaire, anticipant ainsi qu’il peut y avoir des différences entre une charge de travail en lecture-écriture et une charge de travail en lecture seule.

$dbName = "sample" 
$serverName = <server name here>
$serveradminLogin = <login here>
$serveradminPassword = <password here>
$desiredMAXDOP_primary = 8
$desiredMAXDOP_secondary_readonly = 1
 
$params = @{
    'database' = $dbName
    'serverInstance' =  $serverName
    'username' = $serveradminLogin
    'password' = $serveradminPassword
    'outputSqlErrors' = $true
    'query' = 'ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = ' + $desiredMAXDOP_primary + ';
    ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = ' + $desiredMAXDOP_secondary_readonly + ';
    SELECT [value], value_for_secondary FROM sys.database_scoped_configurations WHERE [name] = ''MAXDOP'';'
  }
  Invoke-SqlCmd @params

Transact-SQL

Vous pouvez utiliser l'éditeur de requête du Portail Azure, SQL Server Management Studio (SSMS) ou Azure Data Studio pour exécuter des requêtes T-SQL sur votre base de données Azure SQL Database.

1. Ouvrez une fenêtre de nouvelle requête.

2. Connectez-vous à la base de données dans laquelle vous souhaitez modifier MAXDOP. Vous ne pouvez pas changer les configurations au niveau de la base de données dans la base de données master.

3. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter.

Configuration MAXDOP au niveau de la base de données

Cet exemple montre comment déterminer la configuration au niveau de la base de données MAXDOP dans la base de données actuelle à l’aide de l’affichage catalogue système sys.database_scoped_configurations.

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

Cet exemple montre comment utiliser l’instruction ALTER DATABASE SCOPED CONFIGURATION pour définir la configuration MAXDOP sur 8. Le paramètre prend immédiatement effet.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

Cet exemple est destiné à être utilisé avec les bases de données Azure SQL Database avec des réplicas en échelle lecture activés, la géoréplicationet les réplicas secondaires Hyperscale. Par exemple, le réplica principal est défini sur un autre MAXDOP par défaut que le réplica secondaire, anticipant ainsi qu’il peut y avoir des différences entre les charges de travail en lecture-écriture et celles en lecture seule. Toutes les instructions sont exécutées sur le réplica principal. La colonne value_for_secondary de sys.database_scoped_configurations contient des paramètres pour le réplica secondaire.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 1;
SELECT [value], value_for_secondary FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';

MAXDOP (indicateur de requête)

Cet exemple montre comment exécuter une requête à l’aide de l’indicateur de requête pour forcer max degree of parallelism à 2.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM SalesLT.SalesOrderDetail  
WHERE UnitPrice < 5  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2);    
GO

MAXDOP (option d'index)

Cet exemple montre comment reconstruire un index à l’aide de l’option d’index pour forcer max degree of parallelism à 12.

ALTER INDEX ALL ON SalesLT.SalesOrderDetail 
REBUILD WITH 
   (     MAXDOP = 12
       , SORT_IN_TEMPDB = ON
       , ONLINE = ON);

Voir aussi

• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
• sys.database_scoped_configurations (Transact-SQL)
• Configurer des opérations d’index parallèles
• Indicateurs de requête (Transact-SQL)
• Définir les options d’index
• Comprendre et résoudre les problèmes de blocage d’Azure SQL Database

Étapes suivantes

• Surveillance et réglage des performances