Konfigurieren des maximalen Grads an Parallelität (max degree of parallelism, MAXDOP) in Azure SQL-Datenbank

Gilt für: Azure SQL-Datenbank

In diesem Artikel wird die Konfigurationseinstellung Max. Grad an Parallelität (max degree of parallelism, MAXDOP) in Azure SQL-Datenbank beschrieben.

Übersicht

Durch MAXDOP wird die abfrageinterne Parallelität in der Datenbank-Engine gesteuert. Höhere MAXDOP-Werte führen in der Regel zu mehr parallelen Threads pro Abfrage und zu einer schnelleren Abfrageausführung.

In Azure SQL-Datenbank ist die Standardeinstellung „MAXDOP“ für jede neue Einzeldatenbank (Singleton) und jeden neuen Pool für elastische Datenbanken auf „8“ festgelegt. Diese Standardeinstellung verhindert eine unnötige Ressourcenverwendung und ermöglicht gleichzeitig die schnellere Ausführung von Abfragen durch die Datenbank-Engine dank paralleler Threads. Normalerweise ist es nicht erforderlich, den MAXDOP-Wert in Azure SQL-Datenbank-Workloads weiter zu konfigurieren, es kann jedoch Vorteile als erweiterte Leistungsoptimierung haben.

Im Allgemeinen ist die Ausführungszeit schneller, wenn die Datenbank-Engine die Ausführung einer Abfrage mit Parallelität auswählt. Allerdings kann übermäßige Parallelität zusätzliche Prozessorressourcen beanspruchen, ohne die Abfrageleistung zu verbessern. Im großen Stil kann sich übermäßige Parallelität negativ auf die Leistung aller Abfragen auswirken, die in der gleichen Datenbank-Engine-Instanz ausgeführt werden. Daher ist das Festlegen einer Obergrenze für die Parallelität eine gängige Leistungsoptimierung in SQL Server-Workloads.

In der folgenden Tabelle wird das Verhalten der Datenbank-Engine bei der Ausführung von Abfragen mit verschiedenen MAXDOP-Werten beschrieben:

Überlegungen

• In Azure SQL-Datenbank können Sie den MAXDOP-Standardwert ändern:

  • Verwenden Sie auf Abfrageebene hierfür den Abfragehinweis für MAXDOP.
  • Auf Datenbankebene verwenden Sie die auf die Datenbank beschränkte Konfiguration für MAXDOP.

• Überlegungen und Empfehlungen für einen langfristigen SQL Server-MAXDOP-Wert gelten für Azure SQL-Datenbank.

• Indizierungsoperationen, bei denen ein Index erstellt oder neu aufgebaut wird bzw. an deren Ende ein gruppierter Index steht, können ressourcenintensiv sein. Sie können den MAXDOP-Wert der Datenbank für Indexvorgänge außer Kraft setzen, indem Sie die MAXDOP-Indexoption in der Anweisung CREATE INDEX oder ALTER INDEX angeben. Der Wert MAXDOP wird zur Ausführungszeit auf die Anweisung angewendet und wird nicht in den Metadaten für den Index gespeichert. Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

• Neben Abfragen und Indexvorgängen steuert die datenbankbezogene Konfigurationsoption auch die Parallelität anderer Anweisungen, bei denen ggf. eine parallele Ausführung verwendet wird. Hierzu zählen etwa „DBCC CHECKTABLE“, „DBCC CHECKDB“ und „DBCC CHECKFILEGROUP“.

Empfehlungen

Das Ändern von MAXDOP für die Datenbank kann sich auf die Abfrageleistung und die Ressourcenverwendung erheblich auswirken – sowohl positiv als auch negativ. Es gibt jedoch keinen einzelnen MAXDOP-Wert, der für alle Workloads optimal ist. Die Empfehlungen zum Festlegen von MAXDOP sind differenziert und hängen von zahlreichen Faktoren ab.

Einige gleichzeitige Spitzenworkloads funktionieren mit einem anderen MAXDOP-Wert möglicherweise besser als andere. Ein ordnungsgemäß konfigurierter MAXDOP-Wert verringert das Risiko von Leistungs- und Verfügbarkeitsproblemen und kann in einigen Fällen zur Kostensenkung beitragen, da Sie eine unnötige Ressourcenverwendung vermeiden und somit auf ein niedrigeres Dienstziel herunterskalieren können.

Übermäßige Parallelität

Ein höherer MAXDOP-Wert verkürzt oft die Dauer für CPU-intensive Abfragen. Übermäßige Parallelität kann jedoch die Leistung anderer gleichzeitiger Workloads verschlechtern, indem andere Abfragen der CPU- und Arbeitsthreadressourcen „verhungern“. In Extremfällen kann eine übermäßige Parallelität sämtliche Ressourcen der Datenbank oder des Pools für elastische Datenbanken verbrauchen, wodurch Abfragetimeouts, Fehler und Anwendungsausfälle verursacht werden.

Übermäßige Parallelität wird äußerst problematisch, wenn mehr gleichzeitige Anforderungen vorhanden sind, als von den durch das Dienstziel bereitgestellten CPU- und Arbeitsthreadressourcen bewältigt werden können. Vermeiden Sie „MAXDOP 0“, um das Risiko potenzieller zukünftiger Probleme aufgrund von übermäßiger Parallelität zu verringern, wenn eine Datenbank zentral hochskaliert wird oder wenn zukünftige Hardwarekonfigurationen in Azure SQL-Datenbank mehr Kerne für dasselbe Datenbankdienstziel bereitstellen.

Ändern von MAXDOP

Falls für Ihre Azure SQL-Datenbank-Workload eine andere MAXDOP-Einstellung als die Standardeinstellung optimal ist, können Sie die T-SQL-Anweisung ALTER DATABASE SCOPED CONFIGURATION verwenden. Beispiele dazu finden Sie unten im Abschnitt Beispiele zur Verwendung von Transact-SQL. Wenn MAXDOP für jede neu erstellte Datenbank auf einen anderen Wert als den Standardwert festgelegt werden soll, fügen Sie diesen Schritt Ihrem Datenbankbereitstellungsprozess hinzu.

Falls der vom Standardwert abweichende MAXDOP-Wert nur für eine kleine Teilmenge der Abfragen in der Workload von Vorteil ist, können Sie MAXDOP auf Abfrageebene außer Kraft setzen, indem Sie den Hinweis „OPTION (MAXDOP)“ hinzufügen. Beispiele dazu finden Sie unten im Abschnitt Beispiele zur Verwendung von Transact-SQL.

Testen Sie Ihre MAXDOP-Konfigurationsänderungen gründlich mit Auslastungstests, die realistische gleichzeitige Abfragelasten umfassen.

Der MAXDOP-Wert für das primäre und das sekundäre Replikat kann unabhängig konfiguriert werden, falls für Ihre Workloads mit Lese-/Schreibzugriff und Ihre schreibgeschützten Workloads jeweils unterschiedliche MAXDOP-Einstellungen optimal sind. Dies gilt für Azure SQL-Datenbank-Instanzen mit horizontaler Leseskalierung, Georeplikation und sekundären Replikaten für Hyperscale. Standardmäßig erben alle sekundären Replikate die MAXDOP-Konfiguration des primären Replikats.

Sicherheit

Berechtigungen

Die Anweisung ALTER DATABASE SCOPED CONFIGURATION muss als Serveradministrator, als Mitglied der Datenbankrolle db_owner oder als Benutzer, dem die Berechtigung ALTER ANY DATABASE SCOPED CONFIGURATION erteilt wurde, ausgeführt werden.

Beispiele

In diesen Beispielen wird die neueste AdventureWorksLT Beispieldatenbank verwendet, wenn die Option SAMPLE für eine neue Einzeldatenbank von Azure SQL-Datenbank ausgewählt wird.

PowerShell

Datenbankbezogene MAXDOP-Konfiguration

In diesem Beispiel wird gezeigt, wie die Anweisung ALTER DATABASE SCOPED CONFIGURATION verwendet wird, um die Konfiguration MAXDOP auf 2 festzulegen. Die Einstellung wird für neue Abfragen sofort wirksam. Das PowerShell-Cmdlet Invoke-SqlCmd führt die T-SQL-Abfragen aus, um die datenbankbezogene MAXDOP-Konfiguration festzulegen und dann zurückzugeben.

$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

Dieses Beispiel ist zur Verwendung bei Azure SQL-Datenbanken mit aktivierten Replikaten für horizontale Leseskalierung, Georeplikation und sekundären Replikaten für Hyperscale in Azure SQL-Datenbank vorgesehen. Beispielsweise wird das primäre Replikat auf einen anderen MAXDOP-Standardwert als das sekundäre Replikat festgelegt. Dabei wird erwartet, dass es Unterschiede zwischen einem Workload mit Lese-/Schreibzugriff und einem schreibgeschützten Workload gibt.

$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

Sie können mit dem Azure-Portal-Abfrage-Editor, SQL Server Management Studio (SSMS) oder Azure Data Studio T-SQL-Abfragen für Ihre Azure SQL-Datenbank ausführen.

1. Öffnen Sie ein neues Abfragefenster.

2. Stellen Sie eine Verbindung mit der Datenbank her, in der Sie MAXDOP ändern möchten. Datenbankbezogene Konfigurationen können nicht in der master-Datenbank geändert werden.

3. Kopieren Sie das folgende Beispiel in das Abfragefenster, und klicken Sie dann auf Ausführen.

Datenbankbezogene MAXDOP-Konfiguration

In diesem Beispiel wird gezeigt, wie die aktuelle datenbankbezogene MAXDOP-Datenbankkonfiguration mithilfe der Systemkatalogsicht sys.database_scoped_configurations ermittelt wird.

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

In diesem Beispiel wird gezeigt, wie die Anweisung ALTER DATABASE SCOPED CONFIGURATION verwendet wird, um die Konfiguration MAXDOP auf 8 festzulegen. Die Einstellung wird sofort wirksam.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

Dieses Beispiel ist für die Verwendung mit Azure SQL-Datenbank-Instanzen mit aktivierten Replikaten für horizontale Leseskalierung, Georeplikation und sekundären Replikaten für Hyperscale konzipiert. Für das Beispiel wird das primäre Replikat auf einen anderen MAXDOP-Wert festgelegt als das sekundäre Replikat, und es wird erwartet, dass es zwischen Workloads mit Lese-/Schreibzugriff und schreibgeschützten Workloads zu Unterschieden kommt. Alle Anweisungen werden für das primäre Replikat ausgeführt. Die Spalte value_for_secondary von sys.database_scoped_configurations enthält Einstellungen für das sekundäre Replikat.

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-Abfragehinweis

In diesem Beispiel wird gezeigt, wie eine Abfrage mithilfe des Abfragehinweises ausgeführt wird, um für max degree of parallelism den Wert 2 zu erzwingen.

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-Indexoption

Dieses Beispiel zeigt, wie Sie einen Index mithilfe der Indexoption neu erstellen, um für max degree of parallelism den Wert 12 zu erzwingen.

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

Siehe auch

• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
• sys.database_scoped_configurations (Transact-SQL)
• Konfigurieren von Parallelindexvorgängen
• Abfragehinweise (Transact-SQL)
• Festlegen von Indexoptionen
• Verstehen und Beheben von Problemen durch Blockierungen in Azure SQL-Datenbank

Nächste Schritte

• Überwachen und Optimieren der Leistung