Lire un plan réel SQL Server à partir des mesures

Un diagramme de plan semble proposer une méthode simple : trouver le plus grand pourcentage et supprimer cet opérateur. Pourtant, ces pourcentages représentent des coûts estimés par l'optimiseur, pas le temps mesuré de l'exécution observée. Une analyse fiable relie la demande exacte, les lignes qui traversent le plan et les ressources consommées.

Identifier précisément le travail exécuté

Conservez le texte, les valeurs et types des paramètres, le niveau de compatibilité et les options de session pertinentes. Coller la requête dans une autre fenêtre avec d'autres paramètres produit une autre expérience. Activez le plan réel dans SSMS ou utilisez STATISTICS XML. Cette collecte exécute effectivement la requête : avec un lot qui modifie des données, elle n'est pas équivalente à consulter un plan estimé.

L'exemple suivant compare une agrégation avant et après la création d'un index. Utilisez une session de test et activez le plan réel avant de lancer le lot.

CREATE TABLE #PlanOrders
(
    OrderId int NOT NULL PRIMARY KEY,
    CustomerId int NOT NULL,
    Amount decimal(12,2) NOT NULL
);
;WITH N AS
(
    SELECT TOP (10000)
        CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS n
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT #PlanOrders (OrderId, CustomerId, Amount)
SELECT n, n % 100, CONVERT(decimal(12,2), n % 250)
FROM N;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT SUM(Amount) AS Total
FROM #PlanOrders WHERE CustomerId = 42;

CREATE INDEX IX_PlanOrders_Customer
ON #PlanOrders (CustomerId) INCLUDE (Amount);

SELECT SUM(Amount) AS Total
FROM #PlanOrders WHERE CustomerId = 42;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
DROP TABLE #PlanOrders;

Les deux SELECT doivent produire le même total. Comparez les lectures logiques dans les messages et les opérateurs d'accès dans chaque plan. Le second dispose d'un index couvrant CustomerId et Amount. Observez néanmoins le choix réel de l'optimiseur au lieu de supposer qu'une icône précise doit apparaître.

La table est volontairement petite pour faciliter l'essai. La sélectivité et la largeur des lignes de production peuvent modifier le compromis. Séparez aussi la préparation de la mesure : la création de l'index figure dans le lot, mais son plan ne décrit pas le SELECT. Sauvegardez les deux plans et leurs mesures sans vider les caches partagés du serveur.

Chercher la première divergence significative

Suivez les données depuis les accès vers le résultat. Comparez lignes estimées et lignes réelles aux transitions importantes. Un filtre prévu pour 100 lignes qui en fournit 200 000 peut expliquer un join coûteux plus loin. Optimiser seulement le tri final risque de conserver cette erreur initiale.

Distinguez lignes lues et lignes transmises. Un seek peut parcourir une grande plage puis éliminer presque toutes les lignes avec un prédicat résiduel. Le mot Seek ne garantit donc pas un faible coût. Inspectez les propriétés, les prédicats de recherche et les prédicats résiduels. À l'inverse, parcourir une petite table peut coûter moins cher que multiplier les recherches aléatoires.

Avec Nested Loops, examinez le nombre d'exécutions internes. Une opération légère répétée 100 000 fois peut devenir la principale dépense. Vérifiez si les estimations affichées s'appliquent à une exécution ou à leur ensemble, notamment lorsque plusieurs threads participent. Une comparaison utile porte sur des grandeurs comparables.

Un lookup, un tri, un spool ou un hash join n'est pas automatiquement un défaut. Demandez quelle fonction l'opérateur remplit et combien de travail il réalise. Supprimer un lookup avec un index supplémentaire augmente potentiellement le stockage et les écritures. Un spool peut éviter des répétitions, tandis qu'un tri peut répondre à un ordre explicitement demandé.

Tester une hypothèse à la fois

Formulez une cause vérifiable : un prédicat résiduel examine trop de données, un filtre asymétrique fausse l'estimation ou des colonnes inutiles élargissent un tri. Effectuez une modification cohérente avec cette cause. Répétez plusieurs cas de paramètres et comparez exactitude du résultat, lectures, CPU, durée et comportement sous concurrence.

Les avertissements d'exécution méritent une analyse, mais ne la remplacent pas. Un débordement sur disque peut pénaliser fortement une charge concurrente et peu affecter une petite requête occasionnelle. Une suggestion d'index manquant reste une proposition issue d'un contexte particulier. Confrontez-la aux index existants et aux besoins d'écriture avant de la déployer.

Enfin, rapprochez le travail serveur du délai ressenti. Peu de CPU et de lectures n'exclut ni blocage, ni attente de mémoire, ni consommation lente des résultats par le client. Le plan réel complète les attentes et les mesures de bout en bout. Gardez le plan initial, les paramètres et la raison précise de la modification pour disposer d'une référence reproductible lors du prochain incident.

Références techniques: Microsoft Learn: Actual execution plans · Microsoft Learn: Showplan operators · Microsoft Learn: STATISTICS IO.