Notes
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
S'applique à :
SQL Server
Cet article décrit comment configurer l’option de configuration du serveur max worker threads dans SQL Server à l’aide de SQL Server Management Studio ou Transact-SQL. L’option max worker threads permet de configurer le nombre de threads de travail disponibles à l’ensemble du processus SQL Server 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 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 votre configuration système, l’attribution d’une valeur spécifique à max worker threads peut permettre d’accroître les performances.
Limites
Le nombre de demandes de requête peut dépasser la valeur définie dans max worker threads, 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 max worker threads ne limite pas tous les threads qui peuvent ê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 certains threads de travail compris dans max worker thread limit, mais utilisent également des threads système (voir Utilisation des threads par les 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 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
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 max worker threads permet à SQL Server de créer un pool de threads de travail afin de servir un grand nombre de demandes 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).
| 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 † | Ordinateur 64 bits |
|---|---|---|
| <= 4 | 256 | 512 |
| > 4 | 256 + ((processeurs logiques - 4) * 8) | 512 †† + ((processeurs logiques - 4) * 16) |
† À 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 Worker maximale par défaut est divisée par 2 pour les machines avec moins de 2 Go de mémoire.
À 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 † | Ordinateur 64 bits |
|---|---|---|
| <= 4 | 256 | 512 |
| > 4 et <= 64 | 256 + ((processeurs logiques - 4) * 8) | 512 †† + ((processeurs logiques - 4) * 16) |
| > 64 | 256 + ((processeurs logiques - 4) * 32) | 512 †† + ((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 Worker maximale 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 que ce comportement ne constitue 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)
Dans l’Explorateur d’objets, cliquez avec le bouton droit sur un serveur et sélectionnez Propriétés.
Sélectionnez le nœud Processeurs.
Dans la zone Nombre maximum de threads de travail, tapez ou sélectionnez une valeur comprise entre 128 et 65 535.
Conseil
Utilisez l’option max worker threads pour définir le nombre de threads de travail disponibles pour les processus SQL Server. Le paramètre par défaut pour max worker threads convient à la plupart des systèmes.
Cependant, selon votre configuration système, l’attribution d’une valeur plus faible à l’option max worker threads permet parfois d’accroître les performances.
Pour plus d’informations, consultez la section Recommandations dans cet article.
Utiliser Transact-SQL
Connectez-vous au Moteur de base de données.
Dans la barre d’outils standard, sélectionnez Nouvelle requête.
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 threadsla valeur900.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.