De maximale mate van parallelle uitvoering (MAXDOP) configureren in Azure SQL Database en SQL Database in Fabric

van toepassing op:Azure SQL DatabaseSQL-database in Fabric

In dit artikel wordt de configuratie-instelling voor maximale mate van parallelle uitvoering (MAXDOP) in Azure SQL Database en SQL Database in Fabric beschreven.

MAXDOP bepaalt parallellisme binnen query's in de database-engine. Hogere MAXDOP-waarden leiden doorgaans tot meer parallelle threads per query en snellere uitvoering van query's.

In Azure SQL Database en SQL Database in Fabric is de standaard MAXDOP-instelling voor elke nieuwe individuele database en elastische pooldatabase 8. Deze standaardinstelling voorkomt onnodig resourcegebruik, terwijl de database-engine nog steeds query's sneller kan uitvoeren met behulp van parallelle threads. Het is doorgaans niet nodig om MAXDOP verder te configureren in Azure SQL Database-workloads, maar het kan voordelen bieden als een geavanceerde oefening voor het afstemmen van prestaties.

Over het algemeen is de uitvoeringstijd sneller als de database-engine ervoor kiest om een query uit te voeren met parallelle uitvoering. Overtollig parallellisme kan echter extra processorbronnen verbruiken zonder queryprestaties te verbeteren. Op schaal kan overtollig parallellisme een negatieve invloed hebben op de queryprestaties voor alle query's die worden uitgevoerd op hetzelfde exemplaar van de database-engine. Normaal gesproken is het instellen van een bovengrens voor parallelle uitvoering een veelvoorkomende oefening voor het afstemmen van prestaties in SQL Server-workloads.

In de volgende tabel wordt het gedrag van de database-engine beschreven bij het uitvoeren van query's met verschillende MAXDOP-waarden:

Considerations

• In Azure SQL Database en SQL Database in Fabric kunt u de standaardWAARDE MAXDOP wijzigen:

  • Op queryniveau gebruikt u de MAXDOPqueryhint.
  • Op databaseniveau gebruikt u de MAXDOPdatabasebereikconfiguratie.

• Langetermijnoverwegingen en aanbevelingen voor SQL Server MAXDOP zijn van toepassing op Azure SQL Database en SQL Database in Fabric.

• Indexbewerkingen die een index maken of herbouwen, of die een geclusterde index verwijderen, kunnen resource-intensief zijn. U kunt de maxDOP-databasewaarde voor indexbewerkingen overschrijven door de optie MAXDOP-index op te geven in de CREATE INDEX- of ALTER INDEX-instructie. De MAXDOP-waarde wordt tijdens de uitvoering toegepast op de instructie en wordt niet opgeslagen in de metagegevens van de index. Zie Parallelle indexbewerkingen configurerenvoor meer informatie.

• Naast query's en indexbewerkingen bepaalt de configuratieoptie voor databasebereik voor MAXDOP ook parallelle uitvoering van andere instructies die parallelle uitvoering kunnen gebruiken, zoals DBCC CHECKTABLE, DBCC CHECKDBen DBCC CHECKFILEGROUP.

Recommendations

Het wijzigen van MAXDOP voor de database kan grote gevolgen hebben voor queryprestaties en resourcegebruik, zowel positief als negatief. Er is echter geen enkele MAXDOP-waarde die optimaal is voor alle workloads. De aanbevelingen voor het instellen van MAXDOP zijn genuanceerd en zijn afhankelijk van veel factoren.

Sommige pieken in gelijktijdige workloads werken mogelijk beter met een andere MAXDOP dan andere. Een correct geconfigureerde MAXDOP moet het risico op prestatie- en beschikbaarheidsincidenten verminderen en in sommige gevallen kosten verlagen door onnodig resourcegebruik te voorkomen en zo omlaag te schalen naar een lagere servicedoelstelling.

Excessive parallelism

Een hogere MAXDOP vermindert vaak de duur van CPU-intensieve query's. Overmatige parallelle verwerking kan echter de prestaties van andere gelijktijdige workloads verslechteren door andere query's CPU- en werkthreadresources te ontnemen. In extreme gevallen kan overmatig parallellisme alle database- of elastische poolresources verbruiken, waardoor querytime-outs, fouten en toepassingsstoringen optreden.

Overmatig parallellisme wordt het meest problematisch wanneer er meer gelijktijdige aanvragen zijn dan de CPU- en werkdraadresources kunnen ondersteunen die worden geleverd door de service-doelstelling. Vermijd MAXDOP 0 het risico op potentiële toekomstige problemen als gevolg van overmatige parallelle uitvoering als een database omhoog wordt geschaald of als toekomstige hardwareconfiguraties meer kernen bieden voor dezelfde databaseservicedoelstelling.

Modify MAXDOP

Als u bepaalt dat een MAXDOP-instelling anders is dan de standaardinstelling optimaal is voor uw workload, kunt u de ALTER DATABASE SCOPED CONFIGURATION T-SQL-instructie gebruiken. Zie de sectie Voorbeelden met behulp van Transact-SQL hieronder voor voorbeelden. Als u MAXDOP wilt wijzigen in een niet-standaardwaarde voor elke nieuwe database die u maakt, voegt u deze stap toe aan uw database-implementatieproces.

Als niet-standaard MAXDOP slechts een kleine subset van query's in de workload oplevert, kunt u MAXDOP op queryniveau overschrijven door de OPTION (MAXDOP) hint toe te voegen. Zie Voorbeelden met behulp van Transact-SQL voor voorbeelden.

Test uw MAXDOP-configuratiewijzigingen grondig met belastingstests met realistische gelijktijdige querybelastingen.

MAXDOP voor de primaire en secundaire replica's kan onafhankelijk worden geconfigureerd als verschillende MAXDOP-instellingen optimaal zijn voor uw lees- en schrijfworkloads en alleen-lezenworkloads. Dit is van toepassing op secundaire replica's van de Azure SQL Database-leesschaal, geo-replicatie en secundaire replica's van de Hyperscale-servicelaag . Standaard nemen alle secundaire replica's de MAXDOP-configuratie van de primaire replica over.

Permissions

De ALTER DATABASE SCOPED CONFIGURATION-instructie moet worden uitgevoerd als de serverbeheerder, als lid van de databaserol db_owner, of als een gebruiker waaraan de ALTER ANY DATABASE SCOPED CONFIGURATION-machtiging is verleend.

Examples

In deze voorbeelden wordt de meest recente AdventureWorksLT voorbeelddatabase gebruikt wanneer de SAMPLE optie wordt gekozen voor een nieuwe individuele database.

PowerShell

Configuratie voor MAXDOP op database-niveau

In dit voorbeeld ziet u hoe u de opdracht ALTER DATABASE SCOPED CONFIGURATION gebruikt om de configuratie MAXDOP in te stellen op 2. De instelling wordt onmiddellijk van kracht voor nieuwe query's. De PowerShell-cmdlet Invoke-SqlCmd- voert de T-SQL-query's uit om in te stellen en de maxdOP-databaseconfiguratie te retourneren.

$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

Dit voorbeeld is bedoeld voor gebruik met Azure SQL Databases met read scale-out replica's die zijn ingeschakeld, geo-replicatieen secundaire replica's van Azure SQL Database Hyperscale. Als voorbeeld is de primaire replica ingesteld op een andere standaard MAXDOP dan de secundaire replica, waarbij verwacht wordt dat er verschillen kunnen zijn tussen een lees-schrijf bewerking en een alleen-lezen workload.

$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

U kunt de Query-editor van Azure Portal gebruiken voor Azure SQL Database, SQL Server Management Studio (SSMS), de mssql-extensie voor Visual Studio Code of de SQL-query-editor in de Fabric-portal om T-SQL-query's uit te voeren.

1. Open een nieuw queryvenster.

2. Maak verbinding met de database waar u MAXDOP wilt wijzigen. U kunt configuraties met databasebereik niet wijzigen in de master-database.

3. Kopieer en plak het volgende voorbeeld in het queryvenster en selecteer uitvoeren.

Configuratie voor MAXDOP op database-niveau

In dit voorbeeld ziet u hoe u de huidige configuratie van de MAXDOP-databasebereik bepaalt met behulp van de sys.database_scoped_configurations systeemcatalogusweergave.

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

In dit voorbeeld ziet u hoe u de opdracht ALTER DATABASE SCOPED CONFIGURATION gebruikt om de configuratie MAXDOP in te stellen op 8. De instelling wordt onmiddellijk van kracht.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

Dit voorbeeld is bedoeld voor gebruik met Azure SQL Databases met uitgeschaalde replica's die zijn ingeschakeld, geo-replicatieen Hyperscale secundaire replica's. De primaire replica is bijvoorbeeld ingesteld op een andere MAXDOP dan de secundaire replica, in de verwachting dat er verschillen kunnen zijn tussen de lees- en schrijfbewerkingen en de alleen-lezen workloads. Alle instructies worden uitgevoerd op de primaire replica. De kolom value_for_secondary van de sys.database_scoped_configurations bevat instellingen voor de secundaire replica.

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

In dit voorbeeld ziet u hoe u een query uitvoert met behulp van de query-hint om de max degree of parallelism tot 2af te dwingen.

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

In dit voorbeeld ziet u hoe u een index herbouwt met behulp van de indexoptie om de max degree of parallelism af te dwingen 12.

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

Next step

Related content

• ALTER DATABASE SCOPED CONFIGURATION(Transact-SQL)
• sys.database_scoped_configurations (Transact-SQL)
• Parallelle indexbewerkingen configureren
• Queryhints (Transact-SQL)
• Indexopties instellen
• Blokkerende problemen begrijpen en oplossen