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

SQL Server peut utiliser plusieurs cœurs CPU 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 example, un scan de table ou une jointure peuvent être répartis sur plusieurs cœurs, 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 et le nombre de cœurs ont été définis à la fin des années 90, quand les serveurs avaient beaucoup moins de puissance et moins de cœurs. 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. Aujourd’hui, avec la puissance des processeurs modernes, une requête ayant un coût de 5 s’exécute en quelques millisecondes.

Demander à SQL Server de coordonner plusieurs cœurs 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).

Valeurs recommandées

En montant cette valeur à 50, vous forcez les petites requêtes (OLTP) à rester sur un seul cœur. 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.

Faites vos tests, c’est une valeur configurée au niveau de l’instance, et vous pouvez l’ajuster à chaud. Dès que l’option est changée, SQL Server l’applique immédiatement pour toutes les nouvelles requêtes.

Max Degree of Parallelism (MAXDOP)

Le Max Degree of Parallelism (MAXDOP) limite le nombre de cœurs CPU qu’une seule requête peut utiliser, ou plus précisément, qu’une section parallélisée d’une requête peut utiliser.

Par défaut, MAXDOP est à 0, ce qui signifie : « Utilise tous les processeurs disponibles ». Théoriquement, vous lirez dans la documentation que SQL Server est censé choisir un nombre optimal de cœurs en fonction de la charge du serveur. En pratique, il utilisera toujours le nombre maximum de cœurs définis. C’est un des mystères de SQL Server.

Si vous avez un serveur avec 32 cœurs et qu’un utilisateur lance une requête relativemement légère (un peu en dessus de 5), SQL Server peut utiliser les 32 cœurs pour scanner une table modeste, et perdre ensuite son temps à la synchronisation des threads.

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 cœurs (l’overhead de synchronisation devient trop lourd).
• Pour l’OLTP (transactionnel) : Une valeur de 4 est souvent le compromis idéal. Cela permet une exécution rapide sans monopoliser le processeur.
• Architecture NUMA : La règle d’or est de ne jamais dépasser le nombre de cœurs physiques d’un seul nœud NUMA.

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.

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

USE [MaBaseDeProduction];
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;
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.

-- Forcer l'utilisation d'un seul cœur
SELECT col1, col2
FROM MaTable
WHERE condition = 1
OPTION (MAXDOP 1);

-- Autoriser jusqu'à 4 cœurs pour cette requête
SELECT col1, col2
FROM MaGrandeTable
INNER JOIN AutreTable ON ...
WHERE condition = 1
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ésolution de blocages : Parfois, forcer MAXDOP 1 sur une requête problématique permet de supprimer les attentes de synchronisation (CXPACKET / CXSYNC_PORT).
• 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
ALTER INDEX IX_MonIndex ON MaTable
REBUILD WITH (MAXDOP = 2, ONLINE = ON);

MAXDOP via Query Store Hints (SQL Server 2022+)

Depuis SQL Server 2022, le Query Store permet d’appliquer automatiquement des hints à des requêtes spécifiques sans modifier leur code source. Cette fonctionnalité est particulièrement utile pour les applications tierces où vous ne pouvez pas modifier les requêtes.

-- Identifier la requête problématique via Query Store
SELECT q.query_id, qt.query_sql_text, rs.avg_duration
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE '%MaTable%'
ORDER BY rs.avg_duration DESC;

-- Appliquer un hint MAXDOP à une requête spécifique
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
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 activer ou désactiver 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.

-- Créer un pool de ressources avec MAXDOP limité
CREATE RESOURCE POOL PoolReporting
WITH (
    MAX_CPU_PERCENT = 50,
    MAX_DOP = 4  -- MAXDOP maximum pour ce pool
);

CREATE RESOURCE POOL PoolOLTP
WITH (
    MAX_CPU_PERCENT = 80,
    MAX_DOP = 2
);

-- Créer des workload groups
CREATE WORKLOAD GROUP GroupReporting
WITH (IMPORTANCE = MEDIUM)
USING PoolReporting;

CREATE WORKLOAD GROUP GroupOLTP
WITH (IMPORTANCE = HIGH)
USING PoolOLTP;

-- 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. OPTION (MAXDOP n) dans la requête → Priorité absolue
2. Query Store Hint → S’applique si la requête correspond au pattern
3. Resource Governor (MAXDOP du workload group) → Limite maximale pour le workload
4. Database Scoped Configuration → Configuration au niveau de la base
5. sp_configure ‘max degree of parallelism’ → Configuration globale de l’instance

La valeur finale utilisée sera la plus restrictive entre ces niveaux. Par exemple, si votre instance est en MAXDOP 8, votre base en MAXDOP 4, et votre Resource Governor en MAXDOP 2, c’est 2 qui s’appliquera.