Configurare max degree of parallelism (opzione di configurazione del server)

Si applica a:SQL Server

Questo articolo descrive come configurare l'opzione di configurazione del server max degree of parallelism (MAXDOP) in SQL Server usando SQL Server Management Studio, Azure Data Studio o Transact-SQL. Quando un'istanza di SQL Server viene eseguita in un computer con più microprocessori o CPU, il motore di database rileva se è possibile usare il parallelismo. Il grado di parallelismo imposta il numero di processori utilizzati per eseguire una singola istruzione per ogni esecuzione di piani paralleli. È possibile utilizzare l'opzione max degree of parallelism per limitare il numero di processori da utilizzare per l'esecuzione di piani paralleli. Per informazioni più dettagliate sul limite impostato da max degree of parallelism (MAXDOP), vedere la sezione Considerazioni in questa pagina. SQL Server valuta i piani di esecuzione parallela per query, operazioni DDL (Data Definition Language) sugli indici, inserimento parallelo, modifica colonna online, raccolta di statistiche parallela e popolamento dei cursori gestiti da keyset e statici.

Prima di iniziare

Considerazioni

• Questa opzione è avanzata e la relativa modifica è riservata ad amministratori di database esperti o a professionisti con certificazione per SQL Server.

• Se l'opzione Affinity Mask non è impostata sul valore predefinito, il numero di processori disponibili per SQL Server in sistemi SMP (Symmetric Multiprocessor) potrebbe risultare ridotto.

• L'impostazione del grado massimo di parallelismo (MAXDOP) su 0 consente a SQL Server di usare tutti i processori disponibili fino a un massimo di 64. Tuttavia, questo non è il valore consigliato per la maggior parte dei casi. Per altre informazioni sui valori consigliati per max degree of parallelism, vedere la sezione Raccomandazioni in questa pagina.

• Per eliminare la generazione di piani paralleli, impostare max degree of parallelism su 1. Impostare il valore su un numero compreso tra 1 e 32.767 per specificare il numero massimo di core del processore che può essere usato durante l'esecuzione di una singola query. Se il valore è maggiore di quello dei processori disponibili, viene utilizzato il numero effettivo di processori disponibili. Se il computer dispone di un unico processore, il valore di max degree of parallelism verrà ignorato.

• Il limite del massimo grado di parallelismo è impostato per ogni attività. Non è un limite per richiesta o per query. Questo significa che durante l'esecuzione di una query parallela, una singola richiesta può generare più attività fino al limite MAXDOP e ogni attività userà un solo ruolo di lavoro e una sola utilità di pianificazione. Per altre informazioni, vedere la sezione Pianificazione delle attività in parallelo in Guida sull'architettura dei thread e delle attività.

• È possibile sostituire il valore di configurazione del server max degree of parallelism:

  • A livello di query, usando l'hint per la queryMAXDOP o l'hint di Query Store.
  • A livello di database, usando la configurazione con ambito databaseMAXDOP.
  • A livello di carico di lavoro, usando l'opzione di configurazione del gruppo di carico di lavoro di Resource GovernorMAX_DOP.

• 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 di max degree of parallelism per le operazioni sugli indici specificando l'opzione per gli indici MAXDOP nell'istruzione per l'indice. 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 al parallelismo delle query e delle operazioni sugli indici, questa opzione controlla anche il parallelismo dei controlli DBCC CHECKTABLE, DBCC CHECKDB e DBCC CHECKFILEGROUP. È possibile disabilitare i piani di esecuzione parallela per queste istruzioni utilizzando il flag di traccia 2528. Per altre informazioni, vedere Flag di traccia (Transact-SQL).

• SQL Server 2022 (16.x) ha introdotto Feedback sul grado di parallelismo (DOP), una nuova funzionalità per migliorare le prestazioni delle query identificando le inefficienze del parallelismo nella ripetizione delle query, in base al tempo trascorso e alle attese. Il feedback sul grado di parallelismo fa parte della famiglia di funzionalità di elaborazione intelligente delle query e indirizza l'utilizzo secondario del parallelismo per ripetere le query. Per informazioni sul feedback sul DOP, vedere Feedback sul grado di parallelismo (DOP).

Consigli

A partire da SQL Server 2016 (13.x), se, all'avvio del servizio, il motore di database rileva più di otto core fisici per ogni nodo o socket NUMA, vengono creati automaticamente nodi soft-NUMA per impostazione predefinita. Il motore di database inserisce processori logici dello stesso core fisico in nodi soft-NUMA diversi. Le raccomandazioni contenute nella tabella seguente consentono di mantenere tutti i thread di lavoro di una query parallela nello stesso nodo soft-NUMA. Questo comportamento permette di migliorare le prestazioni delle query e la distribuzione dei thread di lavoro tra i nodi NUMA per il carico di lavoro. Per altre informazioni, vedere Soft-NUMA.

A partire da SQL Server 2016 (13.x), usare le linee guida seguenti quando si configura il valore di configurazione del server max degree of parallelism:

Da SQL Server 2008 (10.0.x) a SQL Server 2014 (12.x), usare le linee guida seguenti quando si configura il valore di configurazione del server max degree of parallelism:

Sicurezza

Autorizzazioni

Le autorizzazioni di esecuzione per sp_configure senza alcun parametro o solo con il primo parametro vengono assegnate per impostazione predefinita a tutti gli utenti. Per eseguire sp_configure con entrambi i parametri per la modifica di un'opzione di configurazione o per l'esecuzione dell'istruzione RECONFIGURE, a un utente deve essere concessa l'autorizzazione a livello di server ALTER SETTINGS. L'autorizzazione ALTER SETTINGS è assegnata implicitamente ai ruoli predefiniti del server sysadmin e serveradmin .

Usare SQL Server Management Studio (SSMS) o Azure Data Studio

In Azure Data Studio installare l'estensione Database Admin Tool Extensions for Windows o usare il metodo T-SQL seguente.

Configurare l’opzione del massimo grado di parallelismo

Queste opzioni modificano MAXDOP per l'istanza.

1. In Esplora oggetti fare clic con il pulsante destro del mouse sull'istanza desiderata e selezionare Proprietà.

2. Selezionare il nodo Avanzate.

3. Nella casella Max Degree of Parallelism selezionare il numero massimo di processori da utilizzare nell'esecuzione di piani paralleli.

Usare Transact-SQL

Configurare l'opzione del massimo grado di parallelismo con T-SQL

1. Connettersi al motore di database con SQL Server Management Studio o Azure Data Studio.

2. Nella barra Standard selezionare Nuova query.

3. Copiare e incollare l'esempio seguente nella finestra di query e selezionare Esegui. In questo esempio si illustra come utilizzare sp_configure per configurare l'opzione max degree of parallelism su 16.

USE AdventureWorks2022;  
GO   
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  
EXEC sp_configure 'max degree of parallelism', 16;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  

Per altre informazioni, vedere Opzioni di configurazione del server (SQL Server).

Completamento: Dopo la configurazione dell'opzione max degree of parallelism

L'impostazione diventa effettiva immediatamente senza dover riavviare il server.

Contenuto correlato

• Elaborazione di query intelligenti nei database SQL
• Guida sull'architettura di elaborazione delle query
• DBCC TRACEON - Flag di traccia (Transact-SQL)
• Hint di Query Store
• Hints (Transact-SQL) - Query
• Hint per la query USE HINT
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
• Opzione di configurazione del server affinity mask
• Opzioni di configurazione del server (SQL Server)
• Guida sull'architettura di elaborazione delle query
• Guida sull'architettura dei thread e delle attività
• sp_configure (Transact-SQL)
• Impostare le opzioni di indice
• Feedback sul grado di parallelismo (DOP)
• RECONFIGURE (Transact-SQL)
• Monitoraggio e ottimizzazione delle prestazioni
• Configurazione di operazioni parallele sugli indici