Configurare il grado massimo di parallelismo (MAXDOP) nel database SQL di Azure

Si applica a: Database SQL di Azure

Il presente articolo descrive le impostazioni di configurazione di max degree of parallelism (MAXDOP) in database SQL di Azure.

Panoramica

MAXDOP controlla il parallelismo all'interno delle query, nel motore di database. I valori MAXDOP più elevati comportano in genere più thread paralleli per ogni query e un'esecuzione più rapida delle stesse.

Nel database SQL di Azure l'impostazione MAXDOP predefinita per ogni nuovo database singolo e database del pool elastico è 8. L’impostazione predefinita impedisce l'utilizzo delle risorse non necessario, consentendo al motore di database di eseguire query più velocemente usando thread paralleli. In genere, non è necessario configurare ulteriormente MAXDOP nei carichi di lavoro di database SQL di Azure, anche se ciò può offrire vantaggi come l’ottimizzazione avanzata delle prestazioni.

In generale, se il motore di database sceglie di eseguire una query usando il parallelismo, il tempo di esecuzione è più veloce. Tuttavia, il parallelismo eccessivo può utilizzare risorse aggiuntive del processore senza migliorare le prestazioni delle query. Su larga scala, il parallelismo eccessivo può influire negativamente sulle prestazioni di tutte le query in esecuzione nella stessa istanza del motore di database. Tradizionalmente, l'impostazione di un limite superiore per il parallelismo era un esercizio comune di ottimizzazione delle prestazioni nei carichi di lavoro di SQL Server.

Nella tabella seguente viene descritto il comportamento del motore di database durante l'esecuzione delle query con valori MAXDOP diversi:

Considerazioni

• In database SQL di Azure, è possibile modificare il valore MAXDOP predefinito:

  • A livello di query, usando l'hint per la query MAXDOP.
  • A livello di database, con la configurazione con ambito database MAXDOP.

• Le considerazioni e le raccomandazioni di SQL Server MAXDOP di SQL Server di lunga durata sono applicabili a database SQL di Azure.

• Le operazioni tramite cui viene creato o ricompilato un indice o eliminato un indice cluster possono richiedere un elevato utilizzo di risorse. È possibile sostituire il valore MAXDOP per le operazioni sugli indici specificando l'opzione per gli indici MAXDOP nell'istruzione CREATE INDEX o ALTER INDEX. Il valore MAXDOP viene applicato all'istruzione al momento dell'esecuzione e non viene archiviato nei metadati dell'indice. Per altre informazioni, vedere Configurazione di operazioni parallele sugli indici.

• Oltre alle query e alle operazioni sugli indici, l'opzione di configurazione con ambito database per MAXDOP controlla anche il parallelismo di altre istruzioni che possono usare l'esecuzione parallela, ad esempio DBCC CHECKTABLE, DBCC CHECKDB o DBCC CHECKFILEGROUP.

Consigli

La modifica di MAXDOP per il database può avere un impatto significativo sulle prestazioni delle query e sull'utilizzo delle risorse, sia positivo sia negativo. Non esiste, tuttavia, un singolo valore MAXDOP ottimale per tutti i carichi di lavoro. Le raccomandazioni per l'impostazione di MAXDOP sono sfumate e dipendono da molteplici fattori.

Alcuni carichi di lavoro simultanei di picco possono funzionare meglio con un diverso MAXDOP rispetto ad altri. Un MAXDOP configurato correttamente deve ridurre il rischio di eventi imprevisti di prestazioni e disponibilità e in alcuni casi può ridurre i costi evitando l'utilizzo delle risorse non necessario e riducendo così il livello a un obiettivo di servizio inferiore.

Eccessivo parallelismo

Un MAXDOP più elevato spesso riduce la durata per le query ad alto utilizzo di CPU. Tuttavia, un parallelismo eccessivo può peggiorare altre prestazioni simultanee del carico di lavoro sovraccaricando altre query di risorse del thread di lavoro e CPU. In casi estremi, un parallelismo eccessivo può utilizzare tutte le risorse del database o del pool elastico, causando timeout delle query, errori o interruzioni dell'applicazione.

Il parallelismo eccessivo diventa più problematico quando sono presenti più richieste simultanee di quanto possa essere supportato dalle risorse della CPU e del thread di lavoro fornite dall'obiettivo di servizio. Evitare MAXDOP 0 per ridurre il rischio di potenziali problemi futuri a causa di un eccessivo parallelismo se un database viene ridimensionato o se le configurazioni hardware future in database SQL di Azure forniscono più core per il medesimo obiettivo di servizio del database.

Modifica di MAXDOP

Se si determina che un'impostazione MAXDOP diversa da quella predefinita è ottimale per il carico di lavoro di database SQL di Azure, è possibile usare l'istruzione ALTER DATABASE SCOPED CONFIGURATION T-SQL. A titolo di esempio, vedere la sezione Esempi che usano Transact-SQL di seguito. Per impostare MAXDOP su un valore non predefinito per ogni nuovo database creato aggiungere questo passaggio al processo di distribuzione del database.

Se il MAXDOP non predefinito offre solo un piccolo subset di query nel carico di lavoro, è possibile eseguire l'override di MAXDOP a livello di query aggiungendo l'hint OPTION (MAXDOP). A titolo di esempio, vedere la sezione Esempi che usano Transact-SQL di seguito.

Testare accuratamente le modifiche alla configurazione di MAXDOP con test di carico che coinvolgano carichi di query simultanei realistici.

MAXDOP per le repliche primarie e secondarie può essere configurato in modo indipendente se le diverse impostazioni MAXDOP sono ottimali per i carichi di lavoro di lettura/scrittura e di sola lettura. In database SQL di Azure ciò vale per repliche secondarie con scalabilità orizzontale in lettura, replica geografica e Hyperscale. Per impostazione predefinita tutte le repliche secondarie ereditano la configurazione MAXDOP della replica primaria.

Sicurezza

Autorizzazioni

L'istruzione ALTER DATABASE SCOPED CONFIGURATION deve essere eseguita da un amministratore del server, un membro del ruolo db_owner del database o un utente a cui è stata concessa l'autorizzazione ALTER ANY DATABASE SCOPED CONFIGURATION.

Esempi

Questi esempi usano il database esemplificativo più recente AdventureWorksLT quando si sceglie l'opzione SAMPLE per un nuovo database singolo di database SQL di Azure.

PowerShell

Configurazione in ambito database MAXDOP

In questo esempio viene illustrato come usare l’istruzione ALTER DATABASE SCOPED CONFIGURATION per impostare la configurazione MAXDOP su 2. L'impostazione ha effetto immediato per nuove query. Il cmdlet PowerShell Invoke-SqlCmd esegue le query T-SQL da impostare e restituisce la configurazione con ambito database 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

Questo esempio viene usato con database SQL di Azure con repliche con scalabilità orizzontale in lettura abilitate, replica geografica e repliche secondarie Hyperscale del database SQL di Azure. Ad esempio, la replica primaria è impostata su un valore MAXDOP predefinito diverso come replica secondaria, anticipando che potrebbero esservi differenze tra un carico di lavoro di sola scrittura e uno di sola lettura.

$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

È possibile usare l’editor di query del portale di Azure, SQL Server Management Studio (SSMS) o Azure Data Studio per eseguire query T-SQL sul database SQL di Azure.

1. Aprire una nuova finestra Query.

2. Connettersi al database in cui si desidera modificare MAXDOP. Non è possibile modificare le configurazioni con ambito database nel database master.

3. Copiare e incollare l'esempio seguente nella finestra di query e selezionare Esegui.

Configurazione in ambito database MAXDOP

In questo esempio viene illustrato come determinare la configurazione con ambito database MAXDOP del database corrente usando la vista del catalogo di sistema sys.database_scoped_configurations.

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

In questo esempio viene illustrato come usare l’istruzione ALTER DATABASE SCOPED CONFIGURATION per impostare la configurazione MAXDOP su 8. L'impostazione ha effetto immediato.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

Questo esempio viene usato con database SQL di Azure con repliche con scalabilità orizzontale in lettura abilitate, replica geografica e repliche secondarie Hyperscale. Ad esempio, la replica primaria è impostata su un valore MAXDOP predefinito diverso rispetto alla replica secondaria, anticipando che potrebbero esservi differenze tra un carico di lavoro di sola scrittura e uno di sola lettura. Tutte le istruzioni sono eseguite nella replica primaria. La colonna value_for_secondary di sys.database_scoped_configurations contiene le impostazioni per la replica secondaria.

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';

Hint per query MAXDOP

In questo esempio viene illustrato come eseguire una query usando l'hint per query per forzare l'oggetto max degree of parallelism in 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 - opzione di indice

In questo esempio viene illustrato come ricompilare un indice usando l'opzione di indice per forzare l'oggetto max degree of parallelism in 12.

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

Vedi anche

• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
• sys.database_scoped_configurations (Transact-SQL)
• Configurazione di operazioni parallele sugli indici
• Hint per la query (Transact-SQL)
• Impostare le opzioni di indice
• Comprendere e risolvere i problemi che causano un blocco per il database SQL di Azure

Passaggi successivi

• Monitoraggio e ottimizzazione delle prestazioni