Configurer max worker threads (option de configuration de serveur)

S’applique à :SQL Server

Cet article explique comment configurer l’option de configuration du serveur Nombre maximal de threads de travail dans SQL Server en utilisant SQL Server Management Studio ou Transact-SQL. L’option max worker threads permet de configurer le nombre de threads de travail disponibles SQL Serverà l’ensemble du processus pour traiter les demandes de requête, la connexion, la déconnexion et les requêtes 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 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.

Limites

• Le nombre de demandes de requête peut dépasser la valeur définie pour dans nombre maximal de threads de travail, auquel cas SQL Server regroupe les threads de travail afin que le prochain thread de travail disponible puisse traiter la demande. 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 de configuration du serveur pour le nombre maximal de threads de travail ne limite pas tous les threads qui pourraient être générés dans le 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 quelques-uns des threads de travail dans la limite maximale de threads de travail mais utilisent aussi des threads système (voir Utilisation des threads par les groupes de disponibilité). Si le nombre de threads configurés est dépassé, vous pouvez utiliser la requête suivante pour obtenir 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 suspectez un problème de performance, celui ne vient probablement pas de la disponibilité des threads. 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. Nous vous conseillons d’identifier la cause racine d’un problème de performance avant de changer le paramètre max worker threads. Pour plus d’informations sur l’évaluation de la performance, consultez Surveiller et régler 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 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 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).

  Nombre de processeurs logiques	Ordinateur 32 bits (jusqu’à SQL Server 2014 (12.x))	Ordinateur 64 bits (jusqu’à SQL Server 2016 (13.x) SP1)	Ordinateur 64 bits (à partir de SQL Server 2016 (13.x) SP2 et SQL Server 2017 (14.x))
  <= 4	256	512	512
  8	288	576	576
  16	352	704	704
  32	480	960	960
  64	736	1472	1472
  128	1248	2496	4480
  256	2272	4544	8576

  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) :

  Nombre de processeurs logiques	Ordinateur 32 bits 1	Ordinateur 64 bits
  <= 4	256	512
  > 4	256 + ((processeurs logiques - 4) * 8)	512 2 + ((processeurs logiques - 4) * 16)

  À 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) :

  Nombre de processeurs logiques	Ordinateur 32 bits 1	Ordinateur 64 bits
  <= 4	256	512
  > 4 et <= 64	256 + ((processeurs logiques - 4) * 8)	512 2 + ((processeurs logiques - 4) * 16)
  > 64	256 + ((processeurs logiques - 4) * 32)	512 2 + ((processeurs logiques - 4) * 32)

  ¹ À 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.

  Conseil

  Pour plus d’informations sur l’utilisation de plus de 64 processeurs logiques, consultez Bonnes pratiques pour l’exécution de SQL Server sur des ordinateurs comportant plus de 64 processeurs.

• 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 qu’il ne s’agisse pas d’un défaut, cela 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.

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXEC sp_configure 'max worker threads', 900;
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 du serveur (SQL Server)
• RECONFIGURE (Transact-SQL)
• sp_configure (Transact-SQL)
• Connexion de diagnostic pour les administrateurs bases de données