Configuration du serveur : nombre maximal de threads de travail

S'applique à : SQL Server

Cet article explique comment configurer l’option de configuration du max worker threads serveur dans SQL Server à l’aide de SQL Server Management Studio ou de Transact-SQL. L’option max worker threads configure le nombre de threads de travail disponibles à l’échelle de SQL Server pour traiter les demandes de requête, la connexion, la déconnexion et les demandes d’application similaires.

SQL Server utilise les services de thread natifs des systèmes d’exploitation pour garantir les conditions suivantes :

• Un ou plusieurs threads prennent simultanément en charge chaque réseau pris en charge par SQL Server.
• Un thread gère les points de contrôle de base de données.
• Un pool de threads gère tous les utilisateurs.

La valeur par défaut pour max worker threads est 0. Cela permet à SQL Server de configurer automatiquement le nombre de threads de travail au démarrage. Ce paramètre par défaut convient à la plupart des systèmes. Toutefois, selon la configuration de votre système, la définition max worker threads d’une valeur spécifique améliore parfois les performances.

Limites

Le nombre réel de demandes de requête peut dépasser la valeur définie dans max worker threads le cas où SQL Server regroupe les threads de travail afin que le thread de travail disponible suivant puisse gérer la requête. Un thread de travail est affecté uniquement à des requêtes actives et est libéré une fois la demande en service. Cela se produit même si la session utilisateur/connexion sur laquelle la requête a été effectuée reste ouverte.

L’option max worker threads de configuration du serveur ne limite pas tous les threads qui peuvent être générés à l’intérieur du moteur. Les threads requis pour des tâches telles que LazyWriter, Checkpoint, Logwriter, Service Broker, Lock Manager ou autres sont générés en dehors de cette limite. Les groupes de disponibilité utilisent certains threads de travail à partir de l’intérieur, max worker thread limit mais utilisent également des threads système (voir Utilisation des threads par groupes de disponibilité). Si le nombre de threads configurés est dépassé, la requête suivante fournit des informations sur les tâches système qui ont généré les threads supplémentaires.

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;

Recommandations

Seul un administrateur de base de données qualifié ou un spécialiste agréé doit changer cette option avancée SQL Server. Si vous pensez qu’il existe un problème de performances, il n’est probablement pas la disponibilité des threads de travail. La cause est plus probablement liée à des activités qui occupent les thread de travail et qui ne les libèrent pas. Les exemples incluent des requêtes de longue durée ou des goulots d’étranglement sur le système (E/S, blocage, attentes de verrous, attentes réseau) qui entraînent des requêtes à attente longue. Il est préférable de trouver la cause racine d’un problème de performances avant de modifier le paramètre max worker threads. Pour plus d’informations sur l’évaluation des performances, consultez Surveiller et optimiser les performances.

Le regroupement de threads permet d'optimiser les performances lorsque de nombreux clients sont connectés au serveur. Habituellement, un thread de système d'exploitation séparé est créé pour chaque demande de requête. Cependant, s'il existe des centaines de connexions au serveur, l'utilisation d'un thread par demande de requête peut consommer de grandes quantités de ressources système. L’option max worker threads permet à SQL Server de créer un pool de threads de travail pour traiter un plus grand nombre de requêtes, ce qui améliore les performances.

Le tableau suivant montre le nombre maximal de threads de travail (quand la valeur est définie sur 0) automatiquement configuré pour différentes combinaisons de processeurs logiques, d’architectures d’ordinateur et de versions de SQL Server, en utilisant la formule : Nombre maximal de Workers par défaut + ((Processeurs logiques - 4) * Workers par processeur).

Jusqu’à SQL Server 2016 (13.x) avec Service Pack 1, le nombre de Workers par processeur dépend seulement de l’architecture (32 bits ou 64 bits) :

À partir de SQL Server 2016 (13.x) SP2 et SQL Server 2017 (14.x), le nombre de Workers par UC dépend de l’architecture et du nombre de processeurs (entre 4 et 64, ou supérieur à 64) :

¹ À compter de SQL Server 2016 (13.x), SQL Server ne peut plus être installé sur un système d’exploitation 32 bits. Les valeurs d’ordinateur 32 bits sont répertoriées pour aider les clients exécutant SQL Server 2014 (12.x) et versions antérieures. Nous vous recommandons d'utiliser 1 024 comme nombre maximal de threads de travail pour une instance de SQL Server exécutée sur un ordinateur 32 bits.

² À compter de SQL Server 2017 (14.x), la valeur Nombre maximal de Workers par défaut est divisée par 2 pour les machines avec moins de 2 Go de mémoire.

Lorsque tous les threads de travail traitent de longues requêtes, SQL Server peut sembler ne plus répondre jusqu'à ce qu'un thread de travail soit terminé et devienne disponible. Bien que ce comportement ne soit pas un défaut, il peut parfois être indésirable. Si un processus semble ne pas répondre et si aucune nouvelle requête n'est traitée, connectez-vous à SQL Server à l'aide de la connexion administrateur dédiée (DAC) et terminez le processus. Pour éviter cette situation, augmentez la valeur de l'option max worker threads.

Autorisations

Les autorisations d’exécution de sp_configure , sans paramètre ou avec le premier paramètre uniquement, sont accordées par défaut à tous les utilisateurs. Pour exécuter sp_configure avec les deux paramètres afin de modifier une option de configuration ou pour exécuter l’instruction RECONFIGURE, un utilisateur doit disposer de l’autorisation ALTER SETTINGS au niveau du serveur. L’autorisation ALTER SETTINGS est implicitement détenue par les rôles serveur fixes sysadmin et serveradmin.

Utiliser SQL Server Management Studio (SSMS)

1. Dans l’Explorateur d’objets, cliquez avec le bouton droit sur un serveur et sélectionnez Propriétés.

2. Sélectionnez le nœud Processeurs.

3. Dans la zone Nombre maximum de threads de travail, tapez ou sélectionnez une valeur comprise entre 128 et 65 535.

Utiliser Transact-SQL

1. Connectez-vous au Moteur de base de données.

2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

3. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter. Cet exemple montre comment utiliser sp_configure pour attribuer à l’option max worker threads la valeur 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
   

Le changement prend effet immédiatement après l’exécution de RECONFIGURE, sans nécessiter le redémarrage du Moteur de base de données.

Contenu connexe

• Options de configuration de serveur
• RECONFIGURE (Transact-SQL)
• sp_configure (Transact-SQL)
• Connexion de diagnostic pour les administrateurs bases de données