Le Cardinality Estimator : l'oracle invisible de SQL Server

Vous avez une requête qui met 200 millisecondes. Vous migrez vers SQL Server 2019. Soudain, la même requête met 20 minutes. Les données sont identiques. Les index sont identiques. Le schéma est identique. Qu'est-ce qui a changé ? Le Cardinality Estimator. Ce composant que personne ne comprend, mais qui décide de tout.

Le Cardinality Estimator — le "CE" pour les intimes — est sans doute le composant le plus important et le moins compris de SQL Server. Il ne génère pas votre plan d'exécution. Il ne lit pas vos données. Il fait quelque chose de plus subtil : il devine combien de lignes chaque opération va retourner. Et cette estimation dicte tout le reste.

Pourquoi l'estimation du nombre de lignes est capitale

L'optimiseur de requêtes de SQL Server est un optimiseur basé sur les coûts. Il évalue plusieurs plans d'exécution possibles et choisit le moins coûteux. Mais pour calculer le coût d'un plan, il a besoin de savoir combien de lignes chaque étape va produire. Parce que ce n'est pas pareil de faire un bookmark lookup sur 10 lignes ou sur 10 millions de lignes.

Si le CE estime 10 lignes, l'optimiseur choisira peut-être un nested loop join. Si le CE estime 10 millions de lignes, il choisira un hash join. Ces deux stratégies sont valides — mais pour des situations opposées. Donnez le mauvais plan à la bonne situation, et votre requête explose.

Le problème ? Le CE travaille avec des statistiques. Des histogrammes. Des échantillons. Il ne voit jamais les vraies données au moment de la compilation. Il devine. Et parfois, il se trompe gravement.

Le redesign de 2014 : pourquoi ça a cassé des choses

SQL Server 2014 a introduit un nouveau Cardinality Estimator. Pour la première fois en presque 20 ans, Microsoft a réécrit les algorithmes d'estimation. C'était nécessaire — l'ancien CE datait de SQL Server 7.0 et faisait des hypothèses obsolètes. Mais le nouveau CE, s'il était meilleur en moyenne, était pire sur certains cas spécifiques.

Et "pire sur certains cas", en production, ça veut dire "votre requête critique est maintenant 100 fois plus lente". D'où les nombreuses migrations qui se sont passées sans problème pendant des mois, jusqu'à ce qu'un rapport mensuel tombe à l'eau.

Microsoft a heureusement prévu la soupape. Le trace flag 9481 force l'ancien CE. Et depuis SQL Server 2016, vous pouvez contrôler le CE par base de données :

-- Forcer l'ancien CE (legacy) pour une base
ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;

-- Vérifier la configuration
SELECT name, value 
FROM sys.database_scoped_configurations 
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';

Mais attention : utiliser le legacy CE, c'est mettre un pansement sur une plaie. Le nouveau CE est meilleur dans la majorité des cas. Si une requête régresse, le bon réflexe n'est pas de désactiver le CE globalement, mais de comprendre pourquoi l'estimation est mauvaise.

Le problème des clés ascendantes (ascending key problem)

Voici un classique. Vous avez une table de transactions avec une colonne DateTransaction. Les statistiques ont été mises à jour la nuit. Aujourd'hui, de nouvelles transactions arrivent avec une date d'aujourd'hui — une date qui n'existe pas dans l'histogramme des statistiques. Le CE ne sait pas combien de lignes ont cette date. Il devine. Et il devine mal.

L'ancien CE supposait que toute valeur hors histogramme avait une sélectivité fixe. Le nouveau CE est plus intelligent — il utilise la densité moyenne. Mais dans les deux cas, si vous demandez les transactions d'aujourd'hui et qu'il y en a déjà 100 000, le CE peut estimer 1 ligne. Et l'optimiseur choisit un plan pour 1 ligne. Bookmark lookup sur 100 000 lignes. Catastrophe.

-- Voir l'histogramme des statistiques
DBCC SHOW_STATISTICS ('Transactions', 'IX_Transactions_DateTransaction') 
WITH HISTOGRAM;

La solution ? Plusieurs options. Les statistiques auto-update sont utiles mais se déclenchent tard (après ~20% de modifications). Vous pouvez forcer une mise à jour plus fréquente, utiliser des statistiques filtrées, ou activer le "ascending key" workaround avec OPTIMIZE FOR UNKNOWN. Mais le vrai fix, c'est de comprendre que ce problème existe et de surveiller les requêtes qui touchent les données les plus récentes.

Les statistiques : 200 steps, c'est parfois trop peu

L'histogramme d'un objet statistique dans SQL Server a au maximum 200 steps. 200 intervalles pour représenter la distribution de vos données. Si votre table a 500 millions de lignes avec une distribution irrégulière, 200 steps ne suffisent pas à capturer la réalité.

-- Lister les statistiques d'une table
SELECT 
    s.name AS StatsName,
    s.auto_created,
    s.user_created,
    sp.rows,
    sp.rows_sampled,
    sp.steps,
    sp.modification_counter,
    sp.last_updated
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('Transactions');

Regardez rows_sampled par rapport à rows. Si l'échantillon est petit, l'histogramme est approximatif. Pour les tables critiques, un UPDATE STATISTICS ... WITH FULLSCAN peut faire une différence énorme. Oui, c'est coûteux sur une grosse table. Mais pas autant qu'un plan d'exécution catastrophique en production.

Le parameter sniffing : votre meilleure amie et votre pire ennemie

Le parameter sniffing, c'est quand SQL Server compile une procédure stockée en regardant les valeurs des paramètres à la première exécution, et réutilise ce plan pour toutes les exécutions suivantes. C'est génial quand les paramètres sont représentatifs. C'est catastrophique quand ils ne le sont pas.

Scénario classique : un utilisateur appelle la procédure avec un paramètre très sélectif (un ClientId précis). Le CE estime 5 lignes. L'optimiseur choisit un plan avec bookmark lookup. Le plan est mis en cache. Ensuite, un autre utilisateur appelle la même procédure avec un paramètre peu sélectif (tous les clients). Le plan en cache est réutilisé. Bookmark lookup sur 3 millions de lignes. Le serveur s'écroule.

-- Désactiver le parameter sniffing pour une procédure
CREATE PROCEDURE dbo.GetCommandes @ClientId INT
WITH RECOMPILE
AS
    SELECT * FROM Commandes WHERE ClientId = @ClientId;

-- Ou optimiser pour une valeur moyenne
CREATE PROCEDURE dbo.GetCommandes @ClientId INT
AS
    SELECT * FROM Commandes 
    WHERE ClientId = @ClientId
    OPTION (OPTIMIZE FOR (@ClientId UNKNOWN));

Le WITH RECOMPILE est un correctif ponctuel. OPTIMIZE FOR UNKNOWN utilise la densité plutôt que la valeur spécifique — moins précis mais plus stable. Le vrai fix, c'est souvent de réécrire la requête ou de diviser la logique selon la sélectivité du paramètre. Mais au moins, vous savez que le problème existe.

Les fonctions sur les colonnes : l'assassin d'estimations

Si vous écrivez WHERE YEAR(DateCommande) = 2024, le CE est incapable d'utiliser l'histogramme de DateCommande. Pourquoi ? Parce que l'histogramme est sur DateCommande, pas sur YEAR(DateCommande). Le CE ne peut pas inverser la fonction. Il devine. Et il devine 1 ligne, ou un pourcentage arbitraire.

La solution est de réécrire avec un prédicat sargable :

-- Mauvais : non-sargable, tue l'estimation
WHERE YEAR(DateCommande) = 2024

-- Bon : sargable, utilise l'histogramme
WHERE DateCommande >= '2024-01-01' 
  AND DateCommande < '2025-01-01'

C'est la règle la plus simple et la plus ignorée de l'optimisation SQL. Une fonction sur une colonne dans un prédicat = statistiques inutilisables = estimation au pif = plan potentiellement catastrophique.

Comment diagnostiquer : EstimateRows vs ActualRows

Le moyen le plus direct de voir si le CE se trompe : comparer les lignes estimées et les lignes réelles dans un plan d'exécution.

-- Activer le plan d'exécution réel
SET STATISTICS XML ON;

-- Lancer votre requête
SELECT c.ClientId, c.Nom, COUNT(*) AS NbCommandes
FROM Clients c
INNER JOIN Commandes cmd ON c.ClientId = cmd.ClientId
WHERE cmd.DateCommande >= '2024-01-01'
GROUP BY c.ClientId, c.Nom
OPTION (RECOMPILE);

-- Désactiver
SET STATISTICS XML OFF;

Dans le plan XML, cherchez <EstimateRows> et comparez avec <ActualRows>. Si l'estimation est de 1 et la réalité de 500 000, vous avez un problème de CE. L'optimiseur a construit un plan pour 1 ligne et l'a exécuté sur 500 000. Chaque opération en aval est sous-estimée : memory grant insuffisant, spill dans tempdb, join inefficace.

Depuis SQL Server 2017, l'Adaptive Query Processing peut corriger certains de ces problèmes en temps réel — memory grant feedback, adaptive joins. Mais ça ne remplace pas une bonne estimation de départ.