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

Si applica a: database SQL di Azuredatabase SQL in Fabric

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

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 del database SQL di Azure, anche se può offrire vantaggi come esercizio avanzato di ottimizzazione 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 il suggerimento di query MAXDOP.
  • A livello di database, con la configurazione con ambito databaseMAXDOP.

• Le considerazioni e le raccomandazioni di lunga data di SQL Server MAXDOP sono applicabili ai 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 CHECKDBe 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 potrebbero funzionare meglio con un MAXDOP diverso rispetto ad altri. Un MAXDOP configurato correttamente deve ridurre il rischio di eventi imprevisti di prestazioni e disponibilità e, in alcuni casi, potrebbe 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 le prestazioni di altri carichi di lavoro simultanei privando le altre query delle risorse della CPU e dei thread di lavoro. 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 dell'eccessivo parallelismo, se un database viene ridimensionato o se le configurazioni hardware future in Azure SQL Database forniranno più core per lo stesso obiettivo di servizio del database.

Modificare 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 diverso da quello predefinito avvantaggia solo un piccolo sottoinsieme di query nel carico di lavoro, è possibile sovrascrivere MAXDOP a livello di query aggiungendo l'hint OPTION (MAXDOP). Per esempi, vedere Esempi che usano Transact-SQL.

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. Questo vale per le repliche secondarie del database SQL di Azure con lettura a scalabilità orizzontale, replica geografica e livello di servizio Hyperscale. Per impostazione predefinita tutte le repliche secondarie ereditano la configurazione MAXDOP della replica primaria.

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 dell'ambito del 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 da quello della replica secondaria, anticipando che possono esserci differenze tra un carico di lavoro di lettura-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 per il database SQL 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 all'interno del database master.

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

Configurazione dell'ambito del 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 i database SQL di Azure con repliche con scalabilità orizzontale in lettura abilitate, repliche geografiche e repliche secondarie Hyperscale. Ad esempio, la replica primaria è impostata su un maxDOP diverso rispetto alla replica secondaria, anticipando che possono esserci differenze tra i carichi di lavoro di lettura/scrittura e 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 di query MAXDOP

In questo esempio viene illustrato come eseguire una query usando il suggerimento per forzare max degree of parallelism a 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);

Passo successivo

Contenuti correlati

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