Configurer l'option de configuration du serveur max worker threads

  • Article

Cette rubrique explique comment configurer l'option de configuration de serveur max worker threads 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 pour les processus SQL Server. SQL Server utilise les services de thread natifs des systèmes d’exploitation afin qu’un ou plusieurs threads prennent en charge chaque réseau pris en charge simultanément, un autre thread gère les points de contrôle de base de données et un pool de threads gère tous les utilisateurs. La valeur par défaut de Nombre maximum de threads de travail 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. Cependant, selon votre configuration système, l'attribution d'une valeur spécifique à l'option Nombre maximum de threads de travail permet parfois d'accroître les performances.

Dans cette rubrique

Avant de commencer

Limitations et restrictions

  • Lorsque le nombre de demandes de requêtes est inférieur au nombre défini dans l'option Nombre maximum de threads de travail, un thread traite chaque demande de requête. Toutefois, si le nombre réel de requêtes dépasse la quantité définie dans les threads de travail maximum, SQL Server met en pool les threads de travail afin que le thread de travail disponible suivant puisse gérer la requête.

Recommandations

  • Cette option est une option avancée et doit être modifiée uniquement par un administrateur de base de données expérimenté ou un technicien SQL Server certifié.

  • 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 Nombre maximum de threads de travail permet à SQL Server de créer un pool de threads de travail afin de servir un grand nombre de demandes de requête, ce qui améliore les performances.

  • Le tableau suivant montre le nombre automatiquement configuré de threads de travail max pour différentes combinaisons de processeurs et de versions de SQL Server.

    Nombre d'unités centrales ordinateur 32 bits Ordinateur 64 bits
    <= 4 processeurs 256 512
    8 processeurs 288 576
    16 processeurs 352 704
    32 processeurs 480 960
    64 processeurs 736 1472
    128 processeurs 4224 4480
    256 processeurs 8320 8576

    Remarque

    Pour obtenir des recommandations concernant l’utilisation de plus de 64 unités centrales, consultez Recommandations pour l’exécution de SQL Server sur des ordinateurs comportant plus de 64 unités centrales.

    Avertissement

    Nous recommandons 1024 comme nombre maximal de threads de travail pour une instance de SQL Server qui s’exécute sur un ordinateur 32 bits.

  • 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. Même s'il ne s'agit pas d'une défaillance, ce comportement 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.

L'option de configuration du serveur max worker threads (nombre maximal de threads de travail) ne prend pas en compte les threads requis pour toutes les tâches système, telles que les groupes de disponibilité, Service Broker, le gestionnaire de verrous et autres. 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 engendré 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  
INNERJOIN 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;

Sécurité

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 d’exécuter l’instruction RECONFIGURE, un utilisateur doit disposer de l’autorisation de niveau serveur ALTER SETTINGS. L'autorisation ALTER SETTINGS est implicitement détenue par les rôles serveur fixes sysadmin et serveradmin .

Utilisation de SQL Server Management Studio

Pour configurer l'option max worker threads

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

  2. Cliquez sur le nœud Processeurs .

  3. Dans la zone de définition du nombre maximum de threads de travail , entrez ou sélectionnez une valeur comprise entre 128 et 32767.

    Utilisez l'option de définition du nombre maximal de threads de travail pour définir le nombre de threads de travail disponibles pour SQL Server . Le paramètre par défaut de max worker threads est adapté à la plupart des systèmes. Cependant, selon votre configuration système, l'attribution d'une valeur plus faible à l'option max worker threads (Nombre maximum de threads de travail) permet parfois d'accroître les performances.

Utilisation de Transact-SQL

Pour configurer l'option max worker threads

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

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

  3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. Cet exemple montre comment utiliser sp_configure pour attribuer à l’option max worker threads la valeur 900.

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

Pour plus d’informations, consultez Options de configuration de serveur (SQL Server).

Suivi : Après avoir configuré l'option Nombre maximum de threads de travail

La modification prend effet immédiatement sans nécessiter le redémarrage des Moteur de base de données.

Voir aussi

RECONFIGURE (Transact-SQL)
Options de configuration du serveur (SQL Server)
sp_configure (Transact-SQL)
Connexion de diagnostic pour les administrateurs de base de données