Parallélisme SQL Server : Configurer MAXDOP et le Seuil de Coût

SQL Server peut utiliser plusieurs threads pour exécuter une seule requête, une technique appelée parallélisme. Cela peut améliorer les performances pour les requêtes lourdes, mais mal configuré, cela peut aussi entraîner des ralentissements significatifs et une contention des ressources CPU.

Plusieurs opérateurs dans un plan d’exécution peuvent être parallélisés, ce qui permet à SQL Server de diviser le travail entre plusieurs threads d’exécution. Par exemple, un scan de table ou une jointure peuvent être répartis sur plusieurs threads, qui vont travailler simultanément pour traiter les données plus rapidement. On peut se dire que l’idée est bonne, mais le multithreading a un coût en termes de gestion et de synchronisation des threads. Il s’agit, donc, de trouver un bon équilibre.

Par défaut, SQL Server est configuré pour déclencher le parallélisme trop rapidement, principalement parce que le seuil de coût et les valeurs par défaut ont été définis à la fin des années 90, quand les serveurs avaient beaucoup moins de puissance. Ces valeurs ne sont plus adaptées aux environnements modernes.

Il y a deux valeurs qu’il faut systématiquement changer dans la configuration de l’instance.

Seuil de coût pour le parallélisme (CTFP)

Le Cost Threshold for Parallelism définit le coût estimé d’une requête à partir duquel SQL Server décide qu’une requête est assez « lourde » pour être divisée entre plusieurs processeurs.

Par défaut, cette valeur est fixée à 5. Ce chiffre date de la fin des années 90. Le coût n’est pas une durée mais une estimation relative. Aujourd’hui, avec la puissance des processeurs modernes, une requête estimée à un coût de 5 s’exécute en général en quelques millisecondes.

Demander à SQL Server de coordonner plusieurs threads pour une tâche aussi infime coûte plus de ressources (en gestion de threads) que l’exécution de la requête elle-même. C’est ce qui provoque souvent l’attente CXPACKET (ou CXSYNC_PORT, depuis SQL Server 2022). L’attente CXCONSUMER, elle, fait partie du fonctionnement normal d’un plan parallèle.

Valeurs recommandées

En montant cette valeur à 50, vous forcez les petites requêtes (OLTP) à rester sur un seul thread. Cela libère les autres processeurs pour gérer davantage de connexions simultanées. Le parallélisme ne sera activé que pour les requêtes de reporting ou de maintenance réellement gourmandes.

Au fil des années, nous avons augmenté cette valeur. Je pense aujourd’hui qu’on peut pousser jusqu’à 70.

Vous pouvez considérer de monter plus haut, par exemple, à 90 ou à 130, selon votre charge de travail. Faites vos tests, c’est une valeur configurée au niveau de l’instance, et vous pouvez l’ajuster à chaud, et revenir sur l’ancienne valeur à tout moment. Dès que l’option est changée et validée par RECONFIGURE, SQL Server l’applique immédiatement pour toutes les nouvelles requêtes. Ce RECONFIGURE vide aussi le cache de plans de toute l’instance : chaque essai, comme chaque retour arrière, provoque une vague de recompilations.

Max Degree of Parallelism (MAXDOP)

Le Max Degree of Parallelism (MAXDOP) limite le nombre de threads par branche parallèle d’un plan d’exécution.

Par défaut, l’option MAXDOP vaut 0, ce qui signifie : « Utilise tous les processeurs disponibles », dans la limite de 64. Depuis SQL Server 2019, le programme d’installation propose une valeur calculée d’après le nombre de processeurs : vérifiez ce qui a été retenu sur vos instances.

Théoriquement, vous lirez dans la documentation que SQL Server est censé choisir un nombre optimal de threads en fonction de la charge du serveur. En pratique, sur un serveur qui ne manque pas de worker threads, il utilise le nombre maximum défini. Il ne descend en dessous que si les worker threads manquent au démarrage de l’exécution, ou, depuis SQL Server 2022, si le DOP feedback juge le parallélisme inefficace pour une requête répétée. Le DOP feedback demande le Query Store en lecture et écriture, et un niveau de compatibilité 160 ou plus. Mesuré sur SQL Server 2025 CU7, il est actif par défaut dans une base neuve.

Si vous avez un serveur avec 32 cœurs et qu’un utilisateur lance une requête relativement légère (un peu au-dessus du seuil de 5), SQL Server peut créer de nombreux threads pour scanner une table modeste, et perdre ensuite son temps à la synchronisation.

Consommation de worker threads

Attention à une confusion courante : MAXDOP ne limite pas directement le nombre de cœurs CPU utilisés par une requête, mais le nombre de threads (workers) par branche parallèle du plan d’exécution.

Un plan d’exécution parallèle peut contenir plusieurs branches (zones parallèles séparées par des opérateurs d’échange comme Distribute Streams, Repartition Streams ou Gather Streams). Chaque branche peut utiliser jusqu’à MAXDOP threads.

Une requête parallèle peut donc consommer plus de threads que la valeur de MAXDOP, car chaque branche parallèle du plan d’exécution utilise jusqu’à MAXDOP threads.

La formule de consommation porte sur les branches qui s’exécutent en même temps :

Threads réservés = (DOP d’exécution × nombre de branches actives simultanément) + 1 thread coordinateur

Par exemple, avec MAXDOP 8 et une requête ayant 3 branches parallèles :

• si les 3 branches s’exécutent en même temps : (8 × 3) + 1 = 25 threads ;
• si deux branches seulement tournent ensemble, comme dans l’exemple de la documentation Microsoft : (8 × 2) + 1 = 17 threads.

Sur une machine à 8 vCPU, SQL Server dispose par défaut de 576 worker threads. Si vous avez de nombreuses requêtes parallèles simultanées, vous pouvez épuiser le pool de worker threads, ce qui provoque des attentes THREADPOOL : les sessions restent alors en attente faute de threads disponibles.

C’est pourquoi il est important de bien dimensionner MAXDOP et le seuil de coût pour éviter que de nombreuses petites requêtes ne parallélisent et n’épuisent les ressources.

Les bonnes pratiques du MAXDOP

• Ne dépassez jamais 8 : Même sur des serveurs massifs, il est rare qu’une requête gagne en efficacité au-delà de 8 threads par branche (l’overhead de synchronisation devient trop lourd). Microsoft est moins strict : sur un serveur à plusieurs nœuds NUMA de plus de 16 processeurs logiques chacun, sa recommandation est la moitié des processeurs logiques du nœud, sans dépasser 16.
• Pour l’OLTP (transactionnel) : Une valeur de 4 est souvent le compromis idéal. Cela permet une exécution rapide sans monopoliser les ressources.
• Architecture NUMA : La règle d’or est de ne jamais dépasser le nombre de processeurs logiques d’un seul nœud NUMA. Depuis SQL Server 2016, ce nœud est le nœud soft-NUMA, créé automatiquement dès qu’un nœud NUMA ou un socket compte plus de huit cœurs physiques.

Appliquer les réglages sur l’instance

Ces modifications s’appliquent à chaud, sans redémarrage de l’instance.

-- Activation des options avancées
EXEC sys.sp_configure N'show advanced options', N'1';
RECONFIGURE;

-- Passage du seuil de coût à 50
EXEC sys.sp_configure N'cost threshold for parallelism', N'50';

-- Limitation du MAXDOP global à 4
EXEC sys.sp_configure N'max degree of parallelism', N'4';

RECONFIGURE;

-------------------------------------------------------------------------------
-- essential information to get from a SQL Server when you open it 
-- for the first time
-- rudi@babaluga.com, go ahead license
-------------------------------------------------------------------------------

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


SELECT 'windows_release' as info, windows_release as value
FROM sys.dm_os_windows_info
UNION ALL
SELECT 'total_physical_memory_gb', total_physical_memory_kb / 1024 / 1024.0
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 
	'NUMA_nodes', COUNT(DISTINCT parent_node_id)
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE' AND is_online = 1
UNION ALL
SELECT 
	'CPUs', COUNT(*)
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE' AND is_online = 1
UNION ALL
SELECT name, value
FROM sys.configurations
WHERE configuration_id = 1581
UNION ALL
SELECT name, value
FROM sys.configurations
WHERE configuration_id = 1544
UNION ALL
SELECT name, value
FROM sys.configurations
WHERE configuration_id = 1539
UNION ALL
SELECT name, value
FROM sys.configurations
WHERE configuration_id = 1538
UNION ALL
SELECT RTRIM(counter_name), cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND object_name LIKE '%Buffer Manager%'
UNION ALL
SELECT N'Buffer cache hit ratio', CAST((ratio.cntr_value * 1.0 / base.cntr_value) * 100.0 AS NUMERIC(5, 2))
FROM sys.dm_os_performance_counters ratio
JOIN sys.dm_os_performance_counters base
	ON ratio.object_name = base.object_name
WHERE RTRIM(ratio.object_name) LIKE N'%:Buffer Manager'
AND ratio.counter_name = N'Buffer cache hit ratio'
AND base.counter_name = N'Buffer cache hit ratio base'
UNION ALL
SELECT  'instant_file_initialization', instant_file_initialization_enabled
FROM    sys.dm_server_services
WHERE   servicename LIKE 'SQL Server (%'
OPTION (RECOMPILE, MAXDOP 1);

Gérer le MAXDOP plus finement

Avant 2016, si vous aviez une base de production (OLTP) et une base de reporting sur la même instance, vous deviez choisir un réglage global qui ne convenait jamais parfaitement aux deux.

Depuis SQL Server 2016, vous pouvez utiliser les Database Scoped Configurations. Cela permet de définir un MAXDOP spécifique pour chaque base de données au sein d’une même instance.

L’option s’applique à la base courante. L’exemple porte sur la base de démonstration PachaDataFormation ; répétez l’opération, avec la valeur qui convient, dans chaque base qui a besoin d’un réglage différent de celui de l’instance.

-- Configurer MAXDOP uniquement pour la base PachaDataFormation
USE [PachaDataFormation];
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;
GO

Cette granularité est un atout majeur pour la consolidation de serveurs, permettant d’isoler les comportements de charge sans multiplier les instances.

MAXDOP comme option de requête

Au-delà des réglages au niveau de l’instance ou de la base de données, SQL Server permet également de spécifier MAXDOP directement dans une requête via l’option OPTION (MAXDOP n).

Utilisez cette approche si vous souhaitez forcer le comportement pour une requête spécifique, sans modifier la configuration globale. Vous pouvez augmenter ou diminuer le parallélisme par rapport à l’option globale.

USE [PachaDataFormation];
GO

-- Forcer l'exécution en série (un seul thread)
SELECT VenteId, DateVente, ClientId
FROM Commerce.Vente
WHERE MagasinId = 18
OPTION (MAXDOP 1);

-- Autoriser jusqu'à 4 threads par branche parallèle
SELECT v.MagasinId, SUM(vp.Quantite * vp.PrixVente) AS ChiffreAffaires
FROM Commerce.Vente AS v
JOIN Commerce.VenteProduit AS vp ON vp.VenteId = v.VenteId
GROUP BY v.MagasinId
OPTION (MAXDOP 4);

Cas d’usage

• Requêtes de reporting ponctuelles : Vous pouvez augmenter temporairement le MAXDOP pour une requête lourde de fin de mois, même si votre base est configurée en MAXDOP 2 pour l’OLTP.
• Réduction des attentes de parallélisme : Parfois, forcer MAXDOP 1 sur une requête problématique permet de supprimer les attentes de synchronisation (CXPACKET / CXSYNC_PORT), puisque le plan devient série.
• Maintenance et index : Lors de reconstructions d’index ou de statistiques, vous pouvez contrôler le parallélisme via MAXDOP pour éviter de saturer le serveur. Cette option est disponible dans de nombreuses commandes de modifications de structure et de commandes administratives, comme le DBCC CHECKDB.

-- Exemple avec REBUILD d'index (ONLINE = ON exige l'édition Enterprise)
ALTER INDEX nix_contact_nom ON Contact.Contact
REBUILD WITH (MAXDOP = 2, ONLINE = ON);

MAXDOP via Query Store Hints (SQL Server 2022+)

Depuis SQL Server 2022, le Query Store permet d’attacher un hint à une requête, identifiée par son query_id, sans modifier son code source. Le hint est conservé et s’applique à chaque exécution. Cette fonctionnalité est particulièrement utile pour les applications tierces où vous ne pouvez pas modifier les requêtes.

USE [PachaDataFormation];
GO

-- Identifier la requête problématique via Query Store
-- avg_duration est en microsecondes ; la requête s'exclut elle-même de la recherche
SELECT q.query_id,
       qt.query_sql_text,
       SUM(rs.count_executions) AS executions,
       SUM(rs.avg_duration * rs.count_executions) / SUM(rs.count_executions) / 1000.0 AS duree_moyenne_ms
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE N'%VenteProduit%'
  AND qt.query_sql_text NOT LIKE N'%query_store_query%'
GROUP BY q.query_id, qt.query_sql_text
ORDER BY duree_moyenne_ms DESC;

-- Appliquer un hint MAXDOP à une requête spécifique (remplacez 12345 par le query_id trouvé)
EXEC sys.sp_query_store_set_hints
    @query_id = 12345,
    @query_hints = N'OPTION(MAXDOP 1)';

-- Vérifier les hints appliqués
SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason_desc
FROM sys.query_store_query_hints;

-- Supprimer un hint si nécessaire
EXEC sys.sp_query_store_clear_hints @query_id = 12345;

Avantages des Query Store Hints

• Applications tierces : Contrôlez le parallélisme de requêtes dans des applications dont vous ne maîtrisez pas le code.
• Gestion centralisée : Tous les hints sont stockés dans le Query Store, facilitant l’audit et la documentation.
• Réversible : Vous pouvez poser ou retirer un hint sans redéploiement applicatif.
• Performance proactive : Identifiez les requêtes problématiques via les statistiques Query Store et appliquez des corrections ciblées.

MAXDOP via Resource Governor

Le Gouverneur de ressources (Resource Governor) permet de contrôler le MAXDOP en fonction du groupe de workload (workload group) auquel appartient une session. Cette approche est idéale pour isoler différents types de charges sur la même instance.

USE Master;
GO

-- Créer les pools de ressources (limite CPU ; le MAXDOP se règle sur le workload group)
CREATE RESOURCE POOL PoolReporting
WITH (
    MAX_CPU_PERCENT = 50
);

CREATE RESOURCE POOL PoolOLTP
WITH (
    MAX_CPU_PERCENT = 80
);

-- Créer des workload groups
CREATE WORKLOAD GROUP GroupReporting
WITH (
    MAX_DOP = 4  -- MAXDOP maximum pour ce groupe
    )
USING PoolReporting;

CREATE WORKLOAD GROUP GroupOLTP
WITH (
    MAX_DOP = 2  -- MAXDOP maximum pour ce groupe
    )
USING PoolOLTP;
GO

-- Créer une fonction de classification pour router les sessions
CREATE FUNCTION dbo.fn_ClassifierMAXDOP()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @WorkloadGroup sysname;

    -- Classifier par nom d'application
    IF APP_NAME() LIKE '%Report%'
        SET @WorkloadGroup = 'GroupReporting';
    ELSE IF APP_NAME() LIKE '%Prod%'
        SET @WorkloadGroup = 'GroupOLTP';
    ELSE
        SET @WorkloadGroup = 'default';

    RETURN @WorkloadGroup;
END;
GO

-- Activer le Resource Governor avec la fonction de classification
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.fn_ClassifierMAXDOP);

ALTER RESOURCE GOVERNOR RECONFIGURE;

Cas d’usage du Resource Governor

• Isolation des workloads : Séparez les requêtes OLTP (MAXDOP faible) des requêtes de reporting (MAXDOP plus élevé) sur la même instance.
• Gestion multi-tenant : Appliquez des limites de parallélisme différentes selon le client ou l’application.
• Contrôle par utilisateur : Limitez le MAXDOP pour certains utilisateurs ou groupes AD.
• Protection contre les runaway queries : Empêchez les requêtes non optimisées de monopoliser tous les cœurs du serveur.

Hiérarchie et priorités du MAXDOP

Avec toutes ces options, voici l’ordre de priorité quand plusieurs niveaux de configuration coexistent :

1. Resource Governor (MAX_DOP du workload group) → Plafond de tous les autres réglages
2. Query Store Hint → Attaché à un query_id, il remplace l’OPTION (MAXDOP n) écrite dans la requête
3. OPTION (MAXDOP n) dans la requête → Écrase la configuration de la base et celle de l’instance
4. Database Scoped Configuration → Configuration au niveau de la base, si elle est différente de 0
5. sp_configure ‘max degree of parallelism’ → Configuration globale de l’instance