Configurazione del server: numero massimo di thread di lavoro

Si applica a: SQL Server

Questo articolo descrive come configurare l'opzione di configurazione del max worker threads server in SQL Server usando SQL Server Management Studio o Transact-SQL. L'opzione max worker threads consente di configurare il numero di thread di lavoro disponibili a livello di SQL Server per elaborare richieste di query, account di accesso, disconnessione e richieste di applicazioni simili.

SQL Server usa i servizi thread nativi dei sistemi operativi per garantire le condizioni seguenti:

• Uno o più thread supportano contemporaneamente ogni rete supportata da SQL Server.
• Un thread gestisce i checkpoint del database.
• Un pool di thread gestisce tutti gli utenti.

Il valore predefinito per max worker threads è 0. In questo modo, in SQL Server è possibile configurare automaticamente il numero di thread di lavoro all'avvio. L'impostazione predefinita è la migliore per la maggior parte dei sistemi. Tuttavia, a seconda della configurazione di sistema, l'impostazione max worker threads di un valore specifico talvolta migliora le prestazioni.

Limiti

Il numero effettivo di richieste di query può superare il valore impostato nel max worker threads qual caso SQL Server esegue il pool di thread di lavoro in modo che il thread di lavoro disponibile successivo possa gestire la richiesta. Un thread di lavoro viene assegnato solo alle richieste attive e viene rilasciato dopo aver soddisfatto la richiesta. Ciò è vero anche se la sessione utente o la connessione su cui è stata effettuata la richiesta rimane aperta.

L'opzione max worker threads di configurazione del server non limita tutti i thread che potrebbero essere generati all'interno del motore. I thread di sistema necessari per attività quali LazyWriter, checkpoint, writer di log, Service Broker, Gestione blocchi o altri vengono generati al di fuori di questo limite. I gruppi di disponibilità usano alcuni thread di lavoro dall'interno max worker thread limit di , ma usano anche thread di sistema (vedere Utilizzo dei thread da parte dei gruppi di disponibilità). Se viene superato il numero di thread configurati, la query seguente fornisce informazioni sulle attività di sistema che hanno generato i thread aggiuntivi.

SELECT s.session_id,
    r.command,
    r.status,
    r.wait_type,
    r.scheduler_id,
    w.worker_address,
    w.is_preemptive,
    w.state,
    t.task_state,
    t.session_id,
    t.exec_context_id,
    t.request_id
FROM sys.dm_exec_sessions AS s
    INNER JOIN sys.dm_exec_requests AS r
        ON s.session_id = r.session_id
    INNER JOIN sys.dm_os_tasks AS t
        ON r.task_address = t.task_address
    INNER JOIN sys.dm_os_workers AS w
        ON t.worker_address = w.worker_address
WHERE s.is_user_process = 0;

Consigli

Questa opzione è avanzata e la relativa modifica è riservata ad amministratori di database esperti o a professionisti con certificazione per SQL Server. Se si sospetta che si verifichi un problema di prestazioni, probabilmente non è la disponibilità dei thread di lavoro. La causa è molto probabilmente correlata alle attività che occupano i thread di lavoro e non li rilasciano. Ad esempio, query con esecuzione prolungata o colli di bottiglia nel sistema (I/O, blocco, attese latch, attese di rete) che causano query con lunghe attese. È consigliabile trovare la causa radice di un problema di prestazioni prima di modificare l'impostazione max worker threads. Per altre informazioni sulla valutazione delle prestazioni, vedere Monitorare e ottimizzare le prestazioni.

La creazione di un pool di thread consente di ottimizzare le prestazioni quando al server si connette un numero elevato di client. In genere viene creato un thread del sistema operativo distinto per ogni richiesta di query. In presenza, tuttavia, di centinaia di connessioni al server, l'utilizzo di un thread per ogni richiesta di query può occupare quantità elevate di risorse di sistema. L'opzione max worker threads consente a SQL Server di creare un pool di thread di lavoro per gestire un numero maggiore di richieste di query, migliorando così le prestazioni.

La tabella seguente visualizza il numero massimo di thread di lavoro configurato automaticamente (quando il valore è impostato su 0) in base a diverse combinazioni di CPU logiche, architettura del computer e versioni di SQL Server, usando la formula: Numero massimo di ruoli di lavoro + ((CPU logiche - 4) * Ruoli di lavoro per CPU).

Fino a SQL Server 2016 (13.x) con Service Pack 1, i Ruoli di lavoro per CPU dipendono solo dall'architettura (a 32 oppure a 64 bit):

A partire da SQL Server 2016 (13.x) SP2 e SQL Server 2017 (14.x), i Ruoli di lavoro per CPU dipendono dall'architettura e dal numero di processori (tra 4 e 64 o maggiori di 64):

¹ A partire da SQL Server 2016 (13.x), SQL Server non può più essere installato in un sistema operativo a 32 bit. I valori per i computer a 32 bit vengono indicati per offrire assistenza ai clienti che eseguono SQL Server 2014 (12.x) e versioni precedenti. Come numero massimo di thread di lavoro per un'istanza di SQL Server in esecuzione in un computer a 32 bit si consiglia il valore 1.024.

² A partire da SQL Server 2017 (14.x), il valore di Default Max Workers è diviso per 2 per computer con meno di 2 GB di memoria.

Quando tutti i thread di lavoro sono attivi con query a esecuzione prolungata, SQL Server potrebbe non rispondere fino a quando un thread di lavoro non viene completato e diventa disponibile. Anche se questo comportamento non è un difetto, a volte può essere indesiderato. Se non si riceve risposta da un processo e non è possibile elaborare alcuna query, connettersi a SQL Server utilizzando la connessione amministrativa dedicata e terminare il processo. Per evitare questo problema, aumentare il numero massimo di thread di lavoro.

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 serversysadmin e serveradmin.

Usare SQL Server Management Studio (SSMS)

1. In Esplora oggetti fare clic con il pulsante destro del mouse su un server e scegliere Proprietà.

2. Selezionare il nodo Processori.

3. Nella casella Max worker threads digitare oppure selezionare un valore compreso tra 128 e 65.535.

Usare Transact-SQL

1. Connettersi al motore di database di.

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 worker threads su 900.

   USE master;
   GO
   
   EXECUTE sp_configure 'show advanced options', 1;
   GO
   
   RECONFIGURE;
   GO
   
   EXECUTE sp_configure 'max worker threads', 900;
   GO
   
   RECONFIGURE;
   GO
   
   EXECUTE sp_configure 'show advanced options', 0;
   GO
   
   RECONFIGURE;
   GO
   

La modifica sarà applicata immediatamente dopo l'esecuzione di RECONFIGURE senza richiedere il riavvio del motore di database.

Contenuto correlato

• Opzioni di configurazione del server
• RECONFIGURE (Transact-SQL)
• sp_configure (Transact-SQL)
• Connessione di diagnostica per gli amministratori di database