Les erreurs de conception d'un expert

Chaque requête est correcte. C'est l'assemblage qui échoue : quinze erreurs de vision globale, et la méthode qui évite chacune.

≈ 16 min de lectureLecture

Quinze erreurs

A · La frontière de la boucle (E1–E3)
B · Le modèle n'est pas prêt (E4–E7)
C · Le temps et la cohérence (E8–E10)
D · L'assemblage neutralise le moteur (E11–E15)

Mesures de plusieurs d'entre elles : cas catastrophiques.

Sur cette page

Connaître le moteur à la perfection n'immunise pas. Les erreurs qui suivent ne viennent pas d'une méconnaissance du T-SQL : chaque requête est correcte. Elles viennent d'un défaut de vision globale, c'est-à-dire d'un traitement pensé requête par requête. Pour chacune : le symptôme, la raison pour laquelle un expert la commet, l'effet sur l'ensemble, et la méthode qui l'évite.

	Erreur	Famille	Méthode en une ligne
E1	La boucle externe invisible	Frontière de la boucle	Le paramètre devient une colonne ; version « jeu » de chaque procédure
E2	Le maillon scalaire	Frontière de la boucle	Bloc pur inline, ou fonction matérialisée en table
E3	Le déclencheur qui itère	Frontière de la boucle	Une instruction, `OUTPUT` pour tracer
E4	Le chemin d'accès oublié	Modèle non prêt	Concevoir pour le traitement global, pas pour la boucle
E5	La dérivée recalculée	Modèle non prêt	Matérialiser en préparation
E6	La règle dans le code	Modèle non prêt	Règles en données, jointes
E7	Les types qui ne s'alignent pas	Modèle non prêt	Contrat de types du modèle
E8	Le contexte qui bouge	Temps et cohérence	Entrées figées
E9	L'ordre et l'unicité implicites	Temps et cohérence	Départage unique, source unique
E10	La préparation périmée	Temps et cohérence	Invalidation et régénération explicites
E11	Le parallélisme neutralisé	Assemblage	Auditer le plan ; tables de travail réelles
E12	Les estimations faussées	Assemblage	Compiler après le chargement
E13	La transaction mal dimensionnée	Assemblage	Lots d'ensembles par plages de clés
E14	Le « au cas où » répété	Assemblage	Unicité par construction ; clés d'abord
E15	Le contrôle absent	Assemblage	Plan de contrôle écrit avant

A. La frontière de la boucle#

E1 · La boucle externe invisible#

Symptôme: Une procédure parfaitement ensembliste, appelée une fois par client, par jour ou par entité, par un curseur, un job, une boucle d'ETL ou un ORM (« N+1 »).
Pourquoi un expert la commet: En revue, la procédure est irréprochable ; on ne voit pas la boucle, qui est dans l'appelant. Le test se fait sur un seul client : quelques millisecondes.
Effet global: Le traitement est en réalité ligne à ligne au niveau supérieur : N appels, N plans, N validations, N déclenchements.
Méthode: Promouvoir le paramètre en colonne : l'identifiant d'entité devient une clé de jointure, de GROUP BY ou de PARTITION BY. Fournir pour toute procédure une version « jeu » qui prend un ensemble (paramètre table ou table de périmètre). Interdire qu'une procédure appelée en boucle soit la seule version disponible.

-- Avant : une procédure traite UN client ; un orchestrateur l'appelle N fois
EXEC dbo.p_ValoriserClient @ClientId = 42;

-- Après : la version « jeu » prend l'ensemble du périmètre, le paramètre devient une colonne
CREATE TYPE dbo.t_Ids AS TABLE (Id int NOT NULL PRIMARY KEY);
GO
CREATE PROCEDURE dbo.p_Valoriser @Clients dbo.t_Ids READONLY AS
    INSERT INTO dbo.Valorisations (ClientId, Jour, Montant)
    SELECT c.ClientId, c.Jour, c.Quantite * tj.Taux
    FROM   dbo.Consommations AS c
    JOIN   @Clients          AS p  ON p.Id = c.ClientId
    JOIN   dbo.Clients       AS cl ON cl.ClientId = c.ClientId
    JOIN   dbo.TauxParJour   AS tj ON tj.SegmentId = cl.SegmentId AND tj.Jour = c.Jour;

E2 · Le maillon scalaire#

Symptôme: Une fonction scalaire non inlinable, une fonction table à instructions multiples, une procédure appelée par ligne, au milieu d'un traitement ensembliste.
Pourquoi un expert la commet: La fonction est lisible, réutilisable, indexée ; on la croit « ensembliste » parce qu'elle contient un SELECT. Elle est inlinée sur SQL Server 2019 sous conditions, et un détail (deux RETURN, une CTE, une fonction dépendante du temps) suffit à la rendre non inlinable.
Effet global: Exécution itérative, sans parallélisme, avec des coûts mal estimés (documentation Microsoft) : le traitement entier hérite de la contrainte du maillon le plus faible.
Méthode: Un bloc pur (fonction table en ligne ou expression) ou, mieux, matérialiser la fonction : calculer son résultat sur le domaine utile en préparation, puis joindre. Vérifier is_inlineable au lieu de le supposer, et le plan au lieu de croire la documentation.

-- Fonction appelée par ligne
SELECT c.ClientId, c.Jour, c.Quantite * dbo.f_TauxApplicable(cl.SegmentId, c.Jour)
FROM   dbo.Consommations AS c JOIN dbo.Clients AS cl ON cl.ClientId = c.ClientId;

-- Table de vérité de la fonction, sur son domaine utile (segments x jours)
SELECT c.ClientId, c.Jour, c.Quantite * ISNULL(tj.Taux, 0)
FROM   dbo.Consommations AS c
JOIN   dbo.Clients AS cl ON cl.ClientId = c.ClientId
LEFT JOIN dbo.TauxParJour AS tj ON tj.SegmentId = cl.SegmentId AND tj.Jour = c.Jour;

-- Le moteur dit si une fonction scalaire est inlinable ; ne pas le supposer
SELECT OBJECT_NAME(object_id) AS fonction, is_inlineable FROM sys.sql_modules WHERE object_id = OBJECT_ID(N'dbo.f_TauxApplicable');

E3 · Le déclencheur qui itère#

Symptôme: Un déclencheur correct (écrit pour traiter tout inserted) qui se déclenche à chaque appel unitaire, ou qui relit d'autres tables ligne par ligne.
Pourquoi un expert la commet: Le déclencheur est ensembliste ; ce qui ne l'est pas, c'est la façon dont on le sollicite.
Effet global: N déclenchements, N écritures d'audit, N validations ; l'audit peut peser autant que le traitement lui-même.
Méthode: Une seule instruction qui écrit et trace (OUTPUT … INTO). Quand le déclencheur est conservé, le solliciter une fois, par une écriture de tout l'ensemble.

-- Audit sans déclencheur par appel : une instruction écrit et trace en même temps
INSERT INTO dbo.Valorisations (ClientId, Jour, Montant)
OUTPUT inserted.ClientId, inserted.Jour, inserted.Montant INTO dbo.Audit_Valo (ClientId, Jour, Montant)
SELECT c.ClientId, c.Jour, c.Quantite * ISNULL(tj.Taux, 0)
FROM   dbo.Consommations AS c
JOIN   dbo.Clients AS cl ON cl.ClientId = c.ClientId
LEFT JOIN dbo.TauxParJour AS tj ON tj.SegmentId = cl.SegmentId AND tj.Jour = c.Jour;

B. Le modèle n'est pas prêt#

E4 · Le chemin d'accès oublié#

Symptôme: Une table rangée pour la saisie (par date, par ordre d'arrivée) ou pour un accès unitaire ; la boucle l'interroge par entité et lit toute la table à chaque tour, ou nécessite un index « ajouté pour la boucle ».
Pourquoi un expert la commet: On optimise ce qu'on voit : la requête de la boucle. L'index manquant est ajouté, et le problème semble résolu.
Effet global: Le coût croît comme nombre d'entités × taille de la table : quadratique. Même avec l'index, la boucle lit N fois, là où le traitement global lit une fois.
Méthode: Concevoir les clés et les index pour le traitement global (parcours ordonné, jointures par hachage ou par fusion), et vérifier que chaque table est lue une fois (SET STATISTICS IO : « Scan count »). Ne pas ajouter un index pour sauver une boucle ; supprimer la boucle.

-- Le traitement global lit chaque table UNE fois : vérifier le plan (un parcours par table)
SET STATISTICS IO ON;
-- ... exécuter le traitement, puis lire « Scan count » et « logical reads » de chaque table.
-- Un « Scan count » proportionnel au nombre d'entités traitées trahit une boucle (ou un index qui la compense).

-- Index pensé pour le traitement : l'ordre de clustering suit l'ordre de lecture du traitement
CREATE TABLE dbo.Consommations
(
    ClientId int NOT NULL, Jour date NOT NULL, Quantite decimal(18,3) NOT NULL,
    CONSTRAINT PK_Conso PRIMARY KEY CLUSTERED (Jour, ClientId)     -- séries chronologiques : append + parcours par période
);

E5 · La dérivée recalculée#

Symptôme: Une hiérarchie parcourue par une CTE récursive pour chaque nœud, des droits recalculés à chaque évaluation, un calendrier reconstruit à chaque appel, une jointure de référence rejouée dans six requêtes.
Pourquoi un expert la commet: Chaque calcul est juste et rapide à l'unité ; personne ne mesure combien de fois il est refait.
Effet global: Le même travail est répété N fois ; le total dépasse largement ce qu'un calcul unique, interrogé par jointure, coûterait.
Méthode: Matérialiser en préparation les dérivées utilisées plus d'une fois : table de fermeture, table d'accès, calendrier, colonnes persistées, vues indexées. La régénérer quand sa source change (voir E10).

-- Fermeture de hiérarchie : calculée UNE fois, interrogée par jointure
CREATE TABLE dbo.Fermeture (Ancetre int NOT NULL, Descendant int NOT NULL, Profondeur tinyint NOT NULL,
                            CONSTRAINT PK_Fermeture PRIMARY KEY CLUSTERED (Ancetre, Descendant));
WITH F AS
(
    SELECT NoeudId AS Ancetre, NoeudId AS Descendant, CAST(0 AS tinyint) AS Profondeur FROM dbo.Noeuds
    UNION ALL
    SELECT f.Ancetre, n.NoeudId, CAST(f.Profondeur + 1 AS tinyint) FROM F AS f JOIN dbo.Noeuds AS n ON n.ParentId = f.Descendant
)
INSERT INTO dbo.Fermeture (Ancetre, Descendant, Profondeur) SELECT Ancetre, Descendant, Profondeur FROM F OPTION (MAXRECURSION 200);

E6 · La règle dans le code#

Symptôme: Des CASE, des IF ou des fonctions qui encodent barèmes, seuils, périodes de validité.
Pourquoi un expert la commet: C'est rapide à écrire, et le code est « la source de vérité » dans l'esprit de son auteur.
Effet global: Une règle ne se joint pas, ne se compare pas, ne se contrôle pas ; chaque évolution est un déploiement, et la règle est recalculée ligne par ligne.
Méthode: Règles en données : tables de barèmes, avec contraintes de cohérence ; expansion en préparation (intervalles → une ligne par jour) ; jointure.

-- Barème à intervalles, stocké en données
CREATE TABLE dbo.Taux (SegmentId tinyint NOT NULL, DateDebut date NOT NULL, Taux decimal(9,4) NOT NULL,
                       CONSTRAINT PK_Taux PRIMARY KEY (SegmentId, DateDebut));
-- Expansion : une ligne par segment et par jour, calculée en préparation
WITH T AS (SELECT SegmentId, DateDebut, Taux, LEAD(DateDebut) OVER (PARTITION BY SegmentId ORDER BY DateDebut) AS DateFin FROM dbo.Taux)
INSERT INTO dbo.TauxParJour (SegmentId, Jour, Taux)
SELECT t.SegmentId, d.Jour, t.Taux
FROM   T AS t JOIN dbo.JoursPerimetre AS d ON d.Jour >= t.DateDebut AND (t.DateFin IS NULL OR d.Jour < t.DateFin);

E7 · Les types qui ne s'alignent pas#

Symptôme: Des clés int d'un côté, bigint ou texte de l'autre ; des collations différentes ; date contre datetime ; des paramètres déclarés avec un autre type que la colonne.
Pourquoi un expert la commet: Chaque requête est vérifiée isolément ; le contrat de types entre étapes n'est celui de personne.
Effet global: Conversions implicites aux frontières : un index n'est plus utilisé comme prévu, une recherche devient un parcours, les estimations se dégradent.
Méthode: Un contrat de types du modèle : une clé a le même type partout, y compris dans les paramètres, les tables de travail et les types table. Contrôler les avertissements de conversion dans les plans.

-- Contrat de types : la clé a le même type partout ; les paramètres aussi
CREATE TABLE dbo.Clients   (ClientId int NOT NULL PRIMARY KEY, Nom nvarchar(100) NULL);
CREATE TABLE dbo.Commandes (CommandeId int NOT NULL PRIMARY KEY, ClientId int NOT NULL);   -- pas bigint, pas varchar
-- Une comparaison entre types différents oblige le moteur à convertir : la colonne la moins prioritaire
-- peut être convertie ligne par ligne, et l'index n'est plus utilisé comme prévu.
-- Le plan d'exécution l'indique : avertissement « Type conversion in expression (CONVERT_IMPLICIT) ... »

C. Le temps et la cohérence#

E8 · Le contexte qui bouge#

Symptôme: GETDATE() appelé dans plusieurs étapes, lecture de la table de base à chaque étape, NEWID() dans une clé de tri.
Pourquoi un expert la commet: Chaque étape est déterministe au moment où elle s'exécute ; c'est l'enchaînement qui ne l'est pas.
Effet global: Un traitement qui franchit minuit, ou qui lit pendant que d'autres écrivent, donne un résultat qui dépend de l'instant : impossible à rejouer, à comparer, à tester.
Méthode: Figer les entrées : une date d'exploitation unique passée en paramètre, un ensemble d'entrée en paramètre table, une lecture sous isolation cohérente quand plusieurs lectures doivent voir le même état.

-- Une seule date d'exploitation, fournie par l'appelant, utilisée partout
CREATE PROCEDURE dbo.p_Traiter @DateExploitation date AS
BEGIN
    INSERT INTO dbo.Etape1 (ClientId, Jour, Quantite) SELECT ClientId, Jour, Quantite FROM dbo.Source WHERE Jour <= @DateExploitation;
    INSERT INTO dbo.Etape2 (ClientId, Jour, Quantite) SELECT ClientId, Jour, Quantite FROM dbo.Etape1 WHERE Jour <= @DateExploitation;
END;
-- À éviter : CAST(GETDATE() AS date) dans chaque étape (le traitement qui franchit minuit change de résultat)
-- Pour figer aussi les LECTURES : lire sous isolation cohérente (SNAPSHOT) ou partir d'un paramètre table

E9 · L'ordre et l'unicité implicites#

Symptôme: Un ORDER BY non unique, un TOP sans ordre, une mise à jour dont la source a plusieurs correspondances, un ROW_NUMBER avec ex æquo.
Pourquoi un expert la commet: Le résultat paraît stable en test : le plan, lui, est resté le même. Rien ne l'impose.
Effet global: Le résultat change avec le plan, le volume ou le parallélisme : un test d'équivalence réussit puis échoue sans qu'on ait rien modifié.
Méthode: Un départage unique dans tout ordre, une source unique garantie par contrainte, un test de reproductibilité (deux exécutions, EXCEPT).

-- Ordre unique : un ex æquo ne doit jamais décider du résultat
SELECT ContactId, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY DateCreation DESC, ContactId DESC) AS Rang FROM dbo.Contacts;

-- Source unique : la jointure d'une mise à jour ne doit retourner qu'une ligne par cible
ALTER TABLE dbo.Categories ADD CONSTRAINT UQ_Categories_Code UNIQUE (Code);

-- Test de reproductibilité : deux exécutions, même résultat
-- (copier le résultat de la première dans une table, rejouer, puis EXCEPT dans les deux sens)

E10 · La préparation périmée#

Symptôme: Une table matérialisée (accès, fermeture, taux par jour) qui ne reflète plus sa source ; ou, à l'inverse, tout matérialiser sans stratégie.
Pourquoi un expert la commet: Dans l'enthousiasme de la préparation, on matérialise ; la question « qui régénère, quand ? » vient après.
Effet global: Des résultats faux mais rapides : le pire des cas, car ils ne se voient pas.
Méthode: Invalidation et régénération explicites : chaque dérivée a une source, un déclencheur de régénération (le chargement qui modifie la source), un contrôle de fraîcheur. Ne matérialiser que ce qui est réutilisé et peu volatil.

-- Préparation versionnée : la dérivée est régénérée explicitement quand sa source change
CREATE PROCEDURE dbo.p_Preparer_Acces AS
BEGIN
    TRUNCATE TABLE dbo.Acces;
    INSERT INTO dbo.Acces (UtilisateurId, PerimetreId)
    SELECT DISTINCT a.UtilisateurId, d.PerimetreId
    FROM   dbo.Appartenances AS a
    JOIN   dbo.GroupeFermeture AS gf ON gf.Groupe = a.GroupeId
    JOIN   dbo.DroitsGroupes   AS d  ON d.GroupeId = gf.Ancetre;
    UPDATE STATISTICS dbo.Acces WITH FULLSCAN;
END;
-- Qui l'appelle ? Le chargement qui modifie Groupes, Appartenances ou DroitsGroupes — jamais le traitement lui-même.
-- Contrôle de fraîcheur : comparer le nombre de lignes attendu à celui de la table préparée.

D. L'assemblage neutralise le moteur#

E11 · Le parallélisme neutralisé#

Symptôme: Un traitement qui n'utilise qu'un seul cœur ; un plan « série » inexpliqué.
Pourquoi un expert la commet: Le détail fautif est loin de la requête : une fonction scalaire, une variable de table modifiée, un indice MAXDOP 1 copié d'un exemple.
Effet global: Le traitement perd son seul levier de passage à l'échelle sans que personne ne s'en aperçoive.
Méthode: Auditer le plan : raison de non-parallélisme, fonctions scalaires, variables de table modifiées (la documentation précise que les requêtes qui modifient une variable de table ne sont pas parallélisées). Remplacer par des tables de travail réelles, indexées, créées en préparation.

-- Variable de table modifiée : pas de plan parallèle (documentation) ; table de travail réelle, indexée, en préparation
CREATE TABLE dbo.Travail_Etape1 (ClientId int NOT NULL, Jour date NOT NULL, Montant decimal(18,4) NOT NULL,
                                 CONSTRAINT PK_Travail_Etape1 PRIMARY KEY (ClientId, Jour));
-- Rechercher la raison d'un plan non parallèle : l'attribut NonParallelPlanReason du plan XML,
-- ou une fonction scalaire non inlinée dans la requête.

E12 · Les estimations faussées par l'enchaînement#

Symptôme: Un plan excellent « en test », mauvais en production : estimation d'une ligne là où il y en a un million.
Pourquoi un expert la commet: Le plan est établi avant que la table de travail ne soit chargée, ou la table de travail est une variable de table : sans statistiques, l'optimiseur suppose qu'elle est vide (documentation Microsoft). Chaque requête est correcte ; c'est le moment de la compilation qui ne l'est pas.
Effet global: Algorithmes de jointure inadaptés, mémoire sous-estimée, déversements : le moteur a « bien » optimisé pour des données qui n'existent pas.
Méthode: Compiler après le chargement : séparer préparation et exécution (deux routines), mettre à jour les statistiques après chargement, OPTION (RECOMPILE) ciblé, valeurs « en dur » (voir la génération de code).

-- Séparer préparation et exécution : l'exécution est compilée APRÈS le chargement
EXEC dbo.p_Preparer;        -- crée, charge, indexe les tables de travail
UPDATE STATISTICS dbo.TauxParJour WITH FULLSCAN;
EXEC dbo.p_Executer;        -- ses plans voient les vraies volumétries
-- Si les deux étaient dans la même procédure, les requêtes sur des objets encore vides à la compilation
-- seraient estimées sur des tables vides (ou recompilées en cours de route).

E13 · La transaction mal dimensionnée#

Symptôme: Une seule instruction de plusieurs centaines de millions de lignes qui sature le journal et bloque tout ; ou, à l'opposé, une validation par ligne.
Pourquoi un expert la commet: Les deux extrêmes viennent du même oubli : le journal et les verrous sont une ressource du traitement, à dimensionner.
Effet global: Un journal qui explose, des verrous longs, ou des milliers de validations.
Méthode: Lots d'ensembles : des plages de clés alignées sur l'index de clustering, une transaction par lot, reprise possible (point de reprise noté). Très peu de requêtes ne veut pas dire « aucun lot » ; cela veut dire « jamais un lot d'une ligne ».

-- Lots d'ENSEMBLES : plages de clés alignées sur l'index de clustering, une transaction par lot, reprise possible
DECLARE @min int = (SELECT MIN(ClientId) FROM dbo.Clients),
        @max int = (SELECT MAX(ClientId) FROM dbo.Clients),
        @pas int = 100000;
WHILE @min <= @max
BEGIN
    INSERT INTO dbo.Valorisations (ClientId, Jour, Montant)
    SELECT c.ClientId, c.Jour, c.Quantite * ISNULL(tj.Taux, 0)
    FROM   dbo.Consommations AS c
    JOIN   dbo.Clients AS cl ON cl.ClientId = c.ClientId
    LEFT JOIN dbo.TauxParJour AS tj ON tj.SegmentId = cl.SegmentId AND tj.Jour = c.Jour
    WHERE  c.ClientId >= @min AND c.ClientId < @min + @pas;
    SET @min += @pas;      -- le lot est un ensemble de 100 000 clients, pas un client
END;

E14 · Le « au cas où » répété#

Symptôme: DISTINCT, ORDER BY, GROUP BY à chaque étape ; SELECT * propagé ; la même sous-requête répétée dans plusieurs CTE.
Pourquoi un expert la commet: Chaque précaution est locale et inoffensive ; elles s'additionnent.
Effet global: Des tris et regroupements inutiles à chaque étape, des matérialisations de colonnes larges, des calculs répétés (une CTE n'est pas un cache).
Méthode: Unicité par construction (clés, contraintes) plutôt que DISTINCT de précaution ; tri seulement en sortie ; propager les clés et joindre les attributs en fin de chaîne ; matérialiser ce qui est utilisé plusieurs fois.

-- Propager les clés, joindre les attributs en fin de chaîne
SELECT s.ClientId, s.Jour, s.Montant, cl.Nom, cl.Region        -- les colonnes larges arrivent à la fin
FROM  (SELECT ClientId, Jour, SUM(Montant) AS Montant FROM dbo.Valorisations GROUP BY ClientId, Jour) AS s
JOIN   dbo.Clients AS cl ON cl.ClientId = s.ClientId;
-- Une dérivée utilisée plus d'une fois est matérialisée, pas répétée dans plusieurs CTE (une CTE n'est pas un cache)

E15 · Le contrôle absent#

Symptôme: Une boucle remplacée par une requête, validée « parce que ça marche sur le jeu de test ».
Pourquoi un expert la commet: La satisfaction d'avoir supprimé la boucle fait oublier de prouver l'équivalence.
Effet global: Une différence silencieuse (NULL, doublons, ex æquo) découverte des mois plus tard.
Méthode: Plan de contrôle écrit avant : comptes, invariants, équivalence dans les deux sens contre l'ancienne version, rejouabilité.

-- Plan de contrôle écrit AVANT le traitement
-- 1) invariants : comptes et sommes
SELECT (SELECT COUNT(*) FROM dbo.Consommations) AS entrees, (SELECT COUNT(*) FROM dbo.Valorisations) AS sorties;
-- 2) équivalence avec l'ancienne version, dans les deux sens (aucune ligne attendue)
SELECT * FROM dbo.Valorisations_ref EXCEPT SELECT * FROM dbo.Valorisations;
SELECT * FROM dbo.Valorisations EXCEPT SELECT * FROM dbo.Valorisations_ref;
-- 3) rejouabilité : deuxième exécution, même résultat

Sources et vérification#

Microsoft Learn — Scalar UDF Inlining — exécution itérative, absence de parallélisme, conditions d'inlining
Microsoft Learn — table (Transact-SQL) — variables de table : statistiques, parallélisme
Microsoft Learn — Query processing architecture guide — estimations, paramètres, plans en cache
Microsoft Learn — Use table-valued parameters — paramètres table