Configurare l'opzione di configurazione del server max worker threads

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2008)noDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

In questo argomento si illustra come configurare l'opzione di configurazione del server max worker threads in SQL ServerSQL Server utilizzando SQL Server Management StudioSQL Server Management Studio o Transact-SQLTransact-SQL. Con l'opzione max worker threads è possibile configurare il numero di thread di lavoro disponibili per i processi di SQL ServerSQL Server . SQL ServerSQL Server vengono utilizzati i servizi thread nativi dei sistemi operativi in modo che uno o più thread supportino ogni rete supportata simultaneamente in SQL ServerSQL Server , che tramite un altro thread vengano gestiti i checkpoint di database e che tutti gli utenti siano gestiti da un pool di thread. Il valore predefinito per max worker threads è 0. In questo modo, in SQL ServerSQL Server è possibile configurare automaticamente il numero di thread di lavoro all'avvio. L'impostazione predefinita è la migliore per la maggior parte dei sistemi. A seconda della configurazione del sistema, tuttavia, l'impostazione di max worker thread su un valore specifico determina talvolta un miglioramento delle prestazioni.

Contenuto dell'argomento

Prima di iniziare

Limitazioni e restrizioni

  • Quando il numero effettivo di richieste di query è inferiore al valore impostato per max worker threads, viene assegnato un thread per la gestione di ogni richiesta. Se, tuttavia, il numero effettivo di richieste di query supera il valore impostato per max worker threads, in SQL ServerSQL Server i thread di lavoro vengono riuniti in un pool. In questo modo, le richieste possono essere gestite dal primo thread di lavoro disponibile.

Indicazioni

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

  • La creazione di un pool di thread consente di ottimizzare le prestazioni quando al server è connesso 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 di migliorare le prestazioni di SQL ServerSQL Server grazie alla creazione di un pool di thread di lavoro per soddisfare una maggiore quantità di richieste di query.

  • Nella tabella seguente viene mostrato il numero massimo di thread di lavoro configurato automaticamente per diverse combinazioni di CPU e versioni di SQL ServerSQL Server.

    Numero di CPU computer a 32 bit Computer a 64 bit
    <= 4 processori 256 512
    8 processori 288 576
    16 processori 352 704
    32 processori 480 960
    64 processori 736 1472
    128 processori 4224 4480
    256 processori 8320 8576
    Nota

    SQL ServerSQL 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 2014SQL Server 2014 e versioni precedenti. Si consiglia 1024 come numero massimo di thread di lavoro per un'istanza di SQL ServerSQL Server in esecuzione in un computer a 32 bit.

  • Se tutti i thread di lavoro sono attivi con query a esecuzione prolungata, si potrebbe non ricevere alcuna risposta da SQL ServerSQL Server finché un thread di lavoro non viene completato e diventa disponibile. Anche se non si tratta di un difetto, questo comportamento può talvolta risultare indesiderato. Se non si riceve risposta da un processo e non è possibile elaborare alcuna query, connettersi a SQL ServerSQL Server utilizzando la connessione amministrativa dedicata (DAC) e terminare il processo. Per evitare questo problema, aumentare il numero massimo di thread di lavoro.

    L'opzione di configurazione del server max worker threads non considera i thread che sono richiesti per tutte le attività di sistema come Gruppi di disponibilità, Service Broker, Gestione blocchi e altri. Se viene superato il numero di thread configurato, la seguente query fornirà 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;  

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 l'esecuzione dell'istruzione RECONFIGURE, è necessario concedere all'utente l'autorizzazione a livello di server ALTER SETTINGS. L'autorizzazione ALTER SETTINGS è assegnata implicitamente ai ruoli predefiniti del server sysadmin e serveradmin .

Utilizzo di SQL Server Management Studio

Per configurare l'opzione max worker threads

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

  2. Fare clic sul nodo Processori .

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

    L'opzione max worker threads consente di configurare il numero di thread di lavoro disponibili per i processi di SQL ServerSQL Server . L'impostazione predefinita di max worker threads è ottimale per la maggior parte dei sistemi. A seconda della configurazione del sistema, tuttavia, l'impostazione di max worker thread su un valore inferiore determina talvolta un miglioramento delle prestazioni.

Utilizzo di Transact-SQL

Per configurare l'opzione max worker threads

  1. Connettersi al Motore di databaseDatabase Engine.

  2. Dalla barra Standard fare clic su Nuova query.

  3. Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui. Questo esempio mostra come usare sp_configure per configurare l'opzione max worker threads su 900.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'max worker threads', 900 ;  
GO  
RECONFIGURE;  
GO  

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

Completamento: Dopo la configurazione dell'opzione max worker threads

La modifica sarà applicata immediatamente senza richiedere il riavvio del Motore di databaseDatabase Engine.

Vedere anche

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