Configurare il grado massimo di parallelismo (opzione di configurazione del server)

Si applica a:SQL Server

Questo articolo descrive come configurare l'opzione di configurazione del max degree of parallelism server (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.

Nota

SQL Server 2019 (15.x) presenta raccomandazioni automatiche per l'impostazione dell'opzione di configurazione del server MAXDOP in base al numero di processori disponibili durante il processo di installazione. L'interfaccia utente del programma di installazione consente di accettare le impostazioni consigliate o di immettere valori personalizzati. Per altre informazioni, vedere Pagina Configurazione del motore di database - MaxDOP.

Nel database SQL di Azure e in Istanza gestita di SQL di Azure, l'impostazione MAXDOP predefinita per ogni nuovo database singolo, database del pool elastico e per ogni istanza gestita è 8. Nel database SQL di Azure la configurazione con ambito database MAXDOP è impostata su 8. In Istanza gestita di SQL di Azure l'opzione di configurazione del server max degree of parallelism (MAXDOP) è impostata su 8.

Per altre informazioni su MAXDOP nel database SQL di Azure, vedere Configurare il massimo grado di parallelismo (MAXDOP) nel database SQL di Azure.

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, potrebbe limitare il numero di processori disponibili per SQL Server nei sistemi SMP (Symmetric MultiProcessing).

  • 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 in fase di 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 paralleli per queste istruzioni usando il flag di traccia 2528. Per altre informazioni, vedere Flag di traccia (Transact-SQL).

  • SQL Server 2022 (16.x) ha introdotto il feedback di Degree of Parallelism (DOP), una nuova funzionalità per migliorare le prestazioni delle query identificando l'inefficienze parallelismo per le query ripetute, in base a tempo trascorso e 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 sui commenti e suggerimenti sulle DOP, vedere Il 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:

Configurazione del server Numero di processori Materiale sussidiario
Server con un singolo nodo NUMA Minore o uguale a otto processori logici Mantenere MAXDOP uguale o inferiore al numero di processori logici
Server con un singolo nodo NUMA Maggiore di otto processori logici Mantenere MAXDOP su 8
Server con più nodi NUMA Minore o uguale a 16 processori logici per nodo NUMA Mantenere MAXDOP uguale o inferiore al numero di processori logici per nodo NUMA
Server con più nodi NUMA Più di 16 processori logici per nodo NUMA Impostare per MAXDOP su un valore pari alla metà del numero di processori logici per nodo NUMA senza superare il valore MAX di 16

Nota

Per nodo NUMA nella tabella precedente si intende il nodo soft-NUMA creato automaticamente da SQL Server 2016 (13.x) e versioni successive oppure il nodo NUMA basato su hardware se soft-NUMA è stato disabilitato. Usare le stesse linee guida quando si imposta l'opzione max degree of parallelism per gruppi di carico di lavoro di Resource Governor. Per altre informazioni, vedere CREATE WORKLOAD GROUP (Transact-SQL).

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 max degree of parallelism server:

Configurazione del server Numero di processori Materiale sussidiario
Server con un singolo nodo NUMA Minore o uguale a otto processori logici Mantenere MAXDOP uguale o inferiore al numero di processori logici
Server con un singolo nodo NUMA Maggiore di otto processori logici Mantenere MAXDOP su 8
Server con più nodi NUMA Minore o uguale a otto processori logici per nodo NUMA Mantenere MAXDOP uguale o inferiore al numero di processori logici per nodo NUMA
Server con più nodi NUMA Maggiore di otto processori logici per nodo NUMA Mantenere MAXDOP su 8

Sicurezza

Autorizzazioni

Le autorizzazioni di esecuzione per sp_configure senza parametri o con solo il primo parametro vengono concesse a tutti gli utenti per impostazione predefinita. Per eseguire sp_configure con entrambi i parametri per modificare un'opzione di configurazione o per eseguire l'istruzione RECONFIGURE, a un utente deve essere concessa l'autorizzazione a livello di server ALTER edizione Standard TTINGS. 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 max degree of parallelism

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 max degree of parallelism 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.