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

Si applica a: Database SQL di Azure

Questo articolo descrive l'impostazione di configurazione max degree of parallelism (MAXDOP) in Azure SQL Database.

Nota

Questo contenuto è incentrato su Azure SQL Database. Azure SQL database si basa sulla versione stabile più recente del motore di database di Microsoft SQL Server, la maggior parte del contenuto è simile anche se le opzioni di risoluzione dei problemi e configurazione differiscono. Per altre informazioni su MAXDOP in SQL Server, vedere Configurare l'opzione di configurazione del server max degree of parallelism.

Panoramica

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

In Azure SQL Database l'impostazione predefinita MAXDOP per ogni nuovo database singolo e database del pool elastico è 8. Questa 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 Azure SQL database, anche se può offrire vantaggi come esercizio avanzato di ottimizzazione delle prestazioni.

Nota

Nel mese di settembre 2020, in base agli anni di telemetria nel servizio di database Azure SQL MAXDOP 8 è stata resa l'impostazione predefinita per i nuovi database, come valore ottimale per l'ampia gamma di carichi di lavoro dei clienti. Questa impostazione predefinita ha consentito di evitare problemi di prestazioni dovuti a un parallelismo eccessivo. In precedenza, l'impostazione predefinita per i nuovi database era MAXDOP 0. MAXDOP non è stato modificato automaticamente per i database esistenti creati prima di settembre 2020.

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 è stata un esercizio comune di ottimizzazione delle prestazioni in SQL Server carichi di lavoro.

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

MAXDOP Comportamento
= 1 Il motore di database usa un singolo thread seriale per eseguire query. I thread paralleli non vengono usati.
> 1 Il motore di database imposta il numero di utilità di pianificazione aggiuntive da usare da thread paralleli sul valore MAXDOP o sul numero totale di processori logici, a qualsiasi valore inferiore.
= 0 Il motore di database imposta il numero di utilità di pianificazione aggiuntive da usare da thread paralleli sul numero totale di processori logici o 64, a qualsiasi valore inferiore.

Nota

Ogni query viene eseguita con almeno un'utilità di pianificazione e un thread di lavoro nell'utilità di pianificazione.

Una query eseguita con parallelismo usa utilità di pianificazione aggiuntive e thread paralleli aggiuntivi. Poiché più thread paralleli possono essere eseguiti nella stessa utilità di pianificazione, il numero totale di thread usati per eseguire una query può essere superiore al valore MAXDOP specificato o al numero totale di processori logici. Per altre informazioni, vedere Pianificazione di attività parallele.

Considerazioni

  • In Azure SQL Database è possibile modificare il valore MAXDOP predefinito:

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

  • Le operazioni tramite cui viene creato o ricompilato un indice o eliminato un indice cluster possono richiedere un elevato utilizzo di risorse. È possibile eseguire l'override del valore MAXDOP del database per le operazioni sugli indici specificando l'opzione di indice 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 e DBCC CHECKFILEGROUP.

Raccomandazioni

La modifica di MAXDOP per il database può avere un impatto significativo sulle prestazioni delle query e sull'utilizzo delle risorse, sia positivi che negativi. Tuttavia, non esiste un singolo valore MAXDOP ottimale per tutti i carichi di lavoro. Le raccomandazioni per l'impostazione di MAXDOP sono sfumate e dipendono da molti fattori.

Alcuni carichi di lavoro simultanei di picco possono 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, può ridurre i costi evitando l'utilizzo delle risorse non necessario e riducendo così il livello a un obiettivo di servizio inferiore.

Parallelismo eccessivo

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

Suggerimento

Si consiglia ai clienti di evitare di impostare MAXDOP su 0 anche se non sembra causare problemi attualmente.

Un parallelismo eccessivo diventa più problematico quando sono presenti più richieste simultanee rispetto a quelle supportate dalle risorse cpu e thread di lavoro fornite dall'obiettivo del servizio. Evitare MAXDOP 0 per ridurre il rischio di potenziali problemi futuri a causa di un parallelismo eccessivo se un database viene ridimensionato o se le configurazioni hardware future in Azure SQL Database forniscono più core per lo stesso obiettivo di servizio del database.

Modifica di MAXDOP

Se si determina che un'impostazione MAXDOP diversa dall'impostazione predefinita è ottimale per il carico di lavoro del database Azure SQL, è possibile usare l'istruzione ALTER DATABASE SCOPED CONFIGURATION T-SQL. Per esempi, 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 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). Per esempi, vedere la sezione Esempi che usano Transact-SQL di seguito.

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

MAXDOP per le repliche primarie e secondarie può essere configurato in modo indipendente se le impostazioni MAXDOP diverse sono ottimali per i carichi di lavoro di sola lettura e di lettura. Questo vale per Azure SQL scalabilità orizzontale in lettura database, replica geografica e repliche secondarie 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 come amministratore del server, come membro del ruolo db_ownerdel database o come utente a cui è stata concessa l'autorizzazione ALTER ANY DATABASE SCOPED CONFIGURATION .

Esempi

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

PowerShell

Configurazione con ambito database MAXDOP

In questo esempio viene illustrato come usare l'istruzione ALTER DATABASE SCOPED CONFIGURATION per impostare la MAXDOP configurazione su 2. L'impostazione diventa effettiva immediatamente per le nuove query. Il cmdlet di 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 è destinato all'uso con database Azure SQL con repliche con scalabilità orizzontale in lettura abilitate, replica geografica e Azure SQL repliche secondarie di Database Hyperscale. Ad esempio, la replica primaria è impostata su un valore MAXDOP predefinito diverso come replica secondaria, anticipando che potrebbero esserci differenze tra un carico di lavoro di sola lettura e un carico di lavoro 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 portale di Azure, SQL Server Management Studio (SSMS) o Azure Data Studio per eseguire query T-SQL sul database Azure SQL.

  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 con 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 MAXDOP configurazione su 8. L'impostazione ha effetto immediato.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

Questo esempio è destinato all'uso con database Azure SQL con repliche con scalabilità orizzontale in lettura abilitate, replica geografica e repliche secondarie Hyperscale. Ad esempio, la replica primaria è impostata su un VALORE MAXDOP diverso rispetto alla replica secondaria, prevedendo che siano presenti differenze tra i carichi di lavoro di lettura/scrittura e di sola lettura. Tutte le istruzioni vengono eseguite nella replica primaria. La value_for_secondary colonna dell'oggetto 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';

MAXDOP - hint per la query

In questo esempio viene illustrato come eseguire una query usando l'hint per la query per forzare l'oggetto 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 index per forzare l'oggetto max degree of parallelism su 12.

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

Vedere anche

Passaggi successivi