Plusieurs des participants au forum SQL Server de Développez demandent comment optimiser une requête : ce billet leur est particulièrement destiné, puisqu’une optimisation de requête ne peut réellement se faire sans son plan d’exécution, le mieux étant aussi d’avoir la sortie de l’option de session SET STATISTICS IO, TIME ON, et le DDL (ordre CREATE TABLE) de la table et de ses index.

Un plan de requête expose la façon dont l’optimiseur de requêtes de SQL Server a calculé la résolution d’une requête que nous lui avons soumis. Ce dernier fait un excellent travail dans la très grande majorité des cas, et pour les autres, il a parfois besoin de l’aide d’un DBA lorsque l’indexation n’est pas l’origine du problème.

SQL Server Management Studio (SSMS) expose les plans de requête estimés et réels.
Le plan de requête estimé expose la suite d’opérateurs logiques et physiques que le moteur a trouvé optimale pour résoudre une requête, c’est à dire celle qui consommera le moins de ressources. Ce plan est basé sur les statistiques de colonnes et d’index. La maintenance et la qualité des statistiques est un un sujet vaste que nous n’aborderons pas ici. On peut obtenir le plan de requête estimé sans exécuter celle-ci en pressant CTRL+L, ou en cliquant sur le bouton ci-dessous, après avoir surligné la requête en question s’il y en a plusieurs dans la fenêtre de requêtes de SSMS.

Le DBA peut se servir du plan de requête estimé pour comprendre un problème de performances, mais il arrive qu’un autre type de plan, dit réel, soit nécessaire. Ce plan peut être généré juste après la fin de l’exécution d’une requête, et permet d’exposer les différences entre le plan estimé et ce qui s’est passé lors de son exécution. De façon similaire au plan de requête estimé, on peut obtenir ce plan de requête en pressant CTRL+M, ou en pressant le bouton suivant dans SSMS avant l’exécution de la requête à étudier.

Visuellement, avec SSMS, aussi bien avec le plan de requête estimé que réel, nous obtenons, pour la requête suivante exécutée sur la base de données exemple AdventureWorks2012 proposée par Microsoft ici (voir les détails de l’installation ici) :

Bien qu’utile, le fait d’avoir à constamment déplacer les curseurs de parcours, surtout lorsque le plan est constitué de nombreux opérateurs, devient vite peu confortable.

Alternativement, SQL Sentry nous offre la possibilité d’utiliser la version gratuite de son outil Plan Explorer, que j’avais exposé ici, et qui continue d’évoluer. On peut télécharger le logiciel ici (en toute fin de page), et il s’intègre automatiquement à SSMS. En effet, il suffit d’effectuer un clic-droit dans une zone vierge du plan et de sélectionner l’option View in SQL Sentry Plan Explorer …

… pour voir l’interface de SQL Sentry Plan Explorer s’ouvrir, et observer une façon différente de disséquer un plan de requête :

Le nombre de lignes échangé entre les opérateurs est exposé directement dans le plan, exposé de façon plus compacte. Par ailleurs, le coût relatif des opérateurs est affiché directement au-dessus de ceux-ci, avec une couleur qui s’assombrit suivant la dépense de ressources qu’ils engendrent. Outre le fait que l’on puisse zoomer sur le plan (utile lorsque le plan de requête est tentaculaire, ou en conférence), les divers onglets présents au bas du panneau du plan d’exécution graphique décrivent des informations souvent utiles, notamment :

• le document XML sous-jacent au plan graphique
• L’arbre du plan, similaire à la sortie que l’on obtient avec SSMS lorsque l’option de session SHOWPLAN_TEXT est positionnée à ON
• Les opérations les plus consommatrices de ressources, comparées à toutes les autres opérations du plan
• Les colonnes spécifiées dans la requête, quel opérateur elles subissent et à travers quel index elles sont accédées
• Le diagramme de jointure, qui permet bien souvent de se rentre compte d’une auto-jointure inopportune
• Les paramètres de la requêtes et leur valeur au moment de la compilation et au moment de l’exécution
• Les expressions (par exemple Expr10NN, où N est un entier, que l’on rencontre parfois dans le plan graphique dans SSMS comme substitut à une expression dans la requête, et que l’on arrive à comprendre seulement en décortiquant le plan XML. Cela permet aussi de détecter les conversions implicites, parfois néfastes aux performances d’exécution d’une requête.)

Les diverses requêtes sont exposées une à une, avec leur coût relatif au coût total du plan : il suffit de cliquer sur l’une des requêtes dans le panneau Results pour les parcourir. Enfin, si l’optimiseur suggère d’ajouter un index (fonctionnalité de suggestion d’index manquants), il suffit de cliquer sur l’opérateur final du plan (le plus en haut et à gauche) pour que l’outil ouvre une fenêtre séparée qui donne, comme dans SSMS, l’instruction de création de l’index.

Passons maintenant au plan d’exécution réel :

Avec la possibilité de montrer l’estimation du nombre de lignes (cardinalité) par rapport au nombre de lignes manipulé par les opérateurs lors de l’exécution de la requête par un simple clic-droit, on détecte très rapidement les problèmes d’estimation de cardinalités : c’est bien souvent à cause de cela que la requête étudiée s’exécute lentement ou consomme beaucoup de ressources. Dans ce cas, l’épaisseur des flèches est largement affectée, et c’est l’effet visuel généré par le passage d’un plan à l’autre qui permet la détection visuelle.

Cerise sur le gâteau : SQL Sentry Plan Explorer permet aussi d’anonymiser un plan de requête, c’est à dire de remplacer le nom réel des tables et autres objets exposés par le plan par un nom générique, et ce en conservant toute la logique du plan. Après un clic sur le bouton suivant, qui se trouve dans la barre d’outils de SQL Sentry Plan Explorer …

… une autre fenêtre s’ouvre, exposant le plan anonymisé : au lieu de trouver le nom des objets réels, on trouve par exemple ObjectN.IndexP, où N et P sont des entiers.

On peut ensuite sauvegarder un tel plan (qu’il soit anonymisé ou non) en vue de le partager sur le forum Développez.com dédié à SQL Server sans exposer d’informations sensibles : un clic sur File > Save As ouvre la fenêtre suivante :

• L’extension .queryplananalysis est le format propriétaire de SQL Sentry. Si vous sauvegardez sous ce type, assurez-vous que la personne avec qui vous partagez le plan de requête dispose aussi de SQL Sentry Plan Explorer.
• L’extension .sqlplan est le format propriétaire de SQL Server Management Studio. Après la sauvegarde, si vous double-cliquez sur un tel document, il s’ouvrira automatiquement avec ce dernier
• L’extension .xml est le format générique de description de documents bien connu. Le document résultant est identique à son homologue .sqlplan. On peut l’utiliser pour automatiser l’extraction de données de plusieurs plans.

Bonne capture de plans !