[SQL Server] Query optimization : petite présentation d’un plan d’exécution

Ce billet présente les principaux éléments associés à un plan d’exécution.

Vous pouvez jeter un coup d’œil ici, si vous souhaitez comprendre les différentes phases de l’optimisation d’une requête.

Présentation et intérêt d’un plan d’exécution

Qu’est-ce qu’un plan d’exécution ?

Un plan d’exécution est une fonctionnalité graphique ou textuelle qui permet de connaître la façon dont l’optimiseur traite une requête à partir des différents éléments qui sont à sa disposition (tables, indexes, contraintes, statistiques,…). C’est un exercice d’optimisation très pratique puisqu’il aide à savoir quelle approche adopter dans l’amélioration de l’exécution des requêtes les plus lentes (i.e., problèmes d’indexation,…).

Un plan d’exécution est généralement constitué d’opérateurs qui sont des étapes opérationnelles au cours desquelles le moteur SQL travaille sur le traitement de la requête concernée. C’est le cas, par exemple, d’étapes telles que le scan de tables, le scan d’index, la recherche sur clé (ou key lookup),…

Il existe 2 types de plans d’exécution :

  • Plan d’exécution estimé, qui indique ce que l’optimiseur pense ce qu’il se passera lors du traitement d’une requête.
  • Plan d’exécution réel, qui montre ce qu’il s’est réellement passé lors du traitement d’une requête.

Généralement, il y a peu de différences entre ces 2 types de plans d’exécution. Toutefois, certains facteurs peuvent contribuer à les différencier significativement, comme par exemple :

  • Des statistiques plus à jour.
  • Plan d’exécution estimé invalide (cas d’une requête qui travaille avec une table qui n’existe pas encore, par exemple, malgré une prise en compte préalable de sa création).
  • Parallélisme potentiellement utilisé.

Réutilisabilité et recompilations

Un plan d’exécution mis en cache lors de la phase de compilation d’une requête peut être réutilisé afin d’optimiser, très souvent, les temps de réponse de cette dernière. Il s’agit d’ailleurs du comportement par défaut de l’optimiseur de requêtes quand son plan d’exécution choisi existe déjà en cache (au sein d’une zone mémoire appelée plan cache).

Toutefois, il existe certains éléments qui peuvent contribuer à la suppression d’un plan d’exécution du cache (outre les méthodes manuelles via DBCC FREEPROCCACHE, par exemple) :

  • Une saturation de la mémoire, forçant le lazy writer à la libérer de la place en cache en supprimant un ou plusieurs plans d’exécution suivant leur ancienneté.
  • L’ancienneté, si elle atteint 0.

La suppression automatique d’un plan d’exécution du cache par le lazy writer est sujet à 2 critères : sa non-utilisation en cours et son coût (ou « âge ») qui doit être proche de zéro.

Son « âge » est calculé en effectuant la multiplication de son coût d’exécution estimé par le nombre de fois où il a été référencé par une (ou des) requête(s), sachant qu’à chaque analyse périodique du plan par le lazy writer, ce dernier effectue la soustraction de son âge par 1. De ce fait, moins souvent un plan est utilisé, plus rapidement son coût atteint zéro et inévitablement il s’expose à toute suppression du cache. Par ailleurs, lorsque l’âge d’un plan atteint zéro, ce dernier n’est pas tout de suite supprimé. Il faut, pour cela, que : 1) il y ait une forte pression de mémoire et que 2) le lazywriter soit en train d’analyser ledit plan (ce qu’il peut faire plus fréquemment en cas de manque de mémoire).

En outre, d’autres éléments peuvent conduire à une phase de recompilation de la requête (c’est-à-dire, à l’utilisation d’un autre plan d’exécution que celui existant déjà en cache) :

  • L’utilisation de la procédure stockée sp_recompile.
  • La mise-à-jour des statistiques utilisées par la requête.
  • Le changement de la structure ou des schémas des tables et/ou des vues utilisées par la requête.
  • La suppression ou modification d’un index utilisé par la requête.
  • Changement des options d’un curseur.

Capture d’un plan d’exécution

La capture du plan d’exécution d’une requête peut se faire de diverses façons :

  • Via le SQL Profiler.
  • Via les DMV (SQL Server 2005 minimum).
  • Via le client SQL Server (à partir de SQL Server 2005) :
    • SSMS :
      • Soit en activant l’option de génération de plan d’exécution à chaque lancement de requête:


      • Soit en faisant un clic-droit dans la fenêtre où est écrite la requête, puis choisir d’inclure le plan d’exécution dans le traitement de la requête, sachant qu’il est possible d’afficher le plan d’exécution estimé (qui, alors, affichera le plan d’exécution graphique de la requête sans avoir à exécuter cette dernière)  :


    • Enterprise Manager (avant SQL Server 2005, pour les nostalgiques) :
      • Soit en choisissant d’activer l’affichage du plan d’exécution de la requête dans la barre d’outils et/ou d’afficher le plan d’exécution estimé (qui ne nécessite pas l’exécution de la requête) :


      • Soit en choisissant de l’activer via le menu contextuel accessible sur clic-droit dans la fenêtre où est écrite la requête à traiter :


  • Via T-SQL :
    • Graphiquement (pour SQL Server 2005 et plus, uniquement) : SET STATISTICS XML ON.
    • Textuellement (toute version) :
      • SET SHOWPLAN_ALL ON ou SET SHOWPLAN_TEXT ON: détail des informations (I/O, temps d’exécution,…), en format texte, du plan d’exécution.
      • SET STATISTIC PROFILE ON: la même chose que le point précédent, mais avec le nombre de lignes affectées, et le nombre d’exécution de chaque opérateur.

Il existe d’autres commandes ou options permettant d’afficher une partie des informations fournies par un plan. C’est le cas, par exemple, de :

  • SET STATISTICS TIME ON, qui permet de capturer le temps d’exécution d’une requête.
  • SET STATISTICS IO ON, qui permet de capturer le coût des écritures et lectures logiques lors de l’exécution de la requête.
A noter que dans le cas de l’exécution de batches SQL (i.e., suite d’instructions SQL), un plan d’exécution (graphique ou non) par instruction sera affiché. Et cela en est de même concernant une requête constituée de sous-requêtes.
Cela est très utile d’autant qu’elle permet de mieux identifier les instructions (ou sous-requêtes) les plus coûteuses, et de ne se concentrer que sur celles-ci.

Analyse d’un plan d’exécution

Les opérateurs de recherche

Le scan

Pour la recherche d’enregistrements à traiter, lors de l’exécution d’une requête, il existe 2 types d’opérateurs de recherche avec lesquels travaille l’optimiseur : le scan et le seek.

Le scan est une opération de parcours des lignes d’une table. On parle alors de balayage de tous les enregistrements de la table traitée. Une table étant une structure de données physiquement constituée d’un pointeur sur l’enregistrement précédent et d’un autre sur l’enregistrement suivant, le moteur SQL, lors du scan de la table, procède au parcours de chaque enregistrement jusqu’à ce qu’il trouve celui ou ceux répondant au bon critère de recherche de la requête.

En soi, cette opération, appelée Table Scan, n’est pas problématique, cependant, elle le devient lorsque la table traitée contient un très (trop) grand nombre d’enregistrements quand on souhaite en retourner qu’une partie. Et en règle générale, c’est ce comportement par défaut qu’adopte l’optimiseur SQL lorsqu’il constate que la table n’est pas indexée.

Dans le cas d’une table comportant un index en cluster (clustered index), lorsque l’optimiseur SQL ne trouve pas le bon index lui permettant de retrouver facilement les enregistrements ou quand la requête à traiter nécessite d’afficher tous les enregistrements d’une table, il effectue ce qu’on appelle Clustered
Index Scan, ou balayage de tous les enregistrements contenus dans l’index en cluster. Cela équivaut à un Table Scan, à la différence que la recherche est, avec le Clustered Index Scan, améliorée dans la mesure où les enregistrements sont physiquement triés, et leurs pages de données liées ce qui améliore le parcours du moteur à travers les pages à traiter.

En effet, dans le cas d’un Table Scan, où les pages de données ne sont pas liées, l’optimiseur SQL, pour les parcourir une par une, est obligé d’utiliser des pages IAM (Index Allocation Map) qui permettent de mapper les extents (unité de gestion d’espace de stockage constituée de 8 pages de données contigües).

Dans le cas du scan d’un index non-cluster, on fait référence tout simplement à l’Index Scan.

Le seek

Le seek est une opération de parcours d’index : l’optimiseur SQL va directement traiter les enregistrements correspondant au critère de recherche à partir du bon index contenant la (les) bonne(s) colonne(s).

S’il fallait faire un classement arbitraire des principaux opérateurs de recherches du plus performant au moins performant, on arriverait à l’ordre suivant :

  1. Clustered Index Seek (tous les enregistrements recherchés ont été directement trouvés par l’optimiseur SQL, et sont déjà ordonnés au sein du bon index en cluster).
  2. Index Seek (tous les enregistrements recherchés ont été directement trouvés par l’optimiseur SQL au sein du bon index non-cluster).
  3. Clustered Index Scan (tout l’index en cluster a été balayé, jusqu’à trouver les bons enregistrements).
  4. Index Scan (tout l’index non-cluster a été balayé, jusqu’à trouver les bons enregistrements).
  5. Table Scan (toute la table est balayée jusqu’à trouver les bons enregistrements. Cette opération est comparable au Clustered Index Scan).

Les flèches inter-opérateurs

Au sein du plan d’exécution graphique, les flèches qui relient les opérateurs sont des flux de données traitées par le moteur SQL pour obtenir les résultats recherchés. Les flèches se lisent de droite à gauche, sachant que la dernière étape du plan d’exécution, correspondant au résultat final de la requête traitée, se situe en haut à gauche du plan d’exécution.

Les flèches permettent de déterminer, en un coup d’œil, les opérations les plus coûteuses. En effet, plus une flèche est épaisse, plus il y a d’enregistrements retournés par l’opérateur d’où elle provient.    

Eléments d’optimisation

Indexation

        Notions d’index

Un index est une structure de données organisée de la même manière qu’un arbre équilibré (Balanced tree ou B-Tree). Il permet, au moteur SQL (et aux systèmes de gestion de bases de données, en général) de retrouver rapidement des enregistrements spécifiques, à l’instar de l’index d’un livre permettant, au lecteur, de retrouver facilement la page ou le mot-clé recherché.

L’importance d’un index est telle que ce dernier a une influence significative sur les performances d’une requête. En effet, une requête utilisant des tables peu, pas ou mal (ou trop) indexées a de grandes chances de rencontrer des problèmes de performances, tandis qu’une requête utilisant des tables bien indexées peut voir ses performances s’améliorer de façon optimale.

Pour plus de détails sur les indexes en cluster et les indexes non-cluster, allez ici.

Exemple de détermination de problèmes d’indexation à partir d’un plan d’exécution

Lors de l’analyse d’un plan d’exécution, un bon indicateur :

  • De l’absence d’indexes (et tout particulièrement d’un index en cluster) dans une table est la présence de l’opérateur Table Scan :

    Prenons en exemple une requête simple, et son plan d’exécution :

SELECT *
FROM USER_ROLE ur
WHERE ur.USER_ROLE='CONSULTER'


Le plan, ci-dessus, indique que la table USER_ROLE est dépourvue d’index. On peut d’ailleurs noter que l’unique flèche est particulièrement épaisse, ce qui signifie que l’opérateur Table Scan a fait un gros travail de traitement d’enregistrements.

  • D’un certain manque d’indexes dans une ou plusieurs tables déjà indexées est la présence de :
    • L’opérateur Index Scan à la place d’Index Seek :

      Imaginons la requête suivante :

SELECT s.NAME, s.DESCRIPTION
FROM SERVICES s
WHERE s.SERVICE_MANAGER='SRVMGR'
GO


A cause d’un manque d’index sur la colonne SERVICE_MANAGER, le moteur SQL est obligé de faire un scan de l’index en cluster de la table SERVICES.

  • L’opérateur Key Lookup (ou Bookmark Lookup sous SQL Server 2000 ou RID Lookup s’il s’agit d’une table non-indexée) :

    Imaginons la requête suivante :

SELECT s.REPORTED_VALUE, s.REPORTED_NAME
FROM RESULT_SPEC s JOIN RESULT r ON s.RESULT_NUMBER= r.RESULT_NUMBER
WHERE s.PRODUCT_SPEC_CODE='33432'
GO

En obtenant le plan d’exécution suivant :


… cela montre que, techniquement parlant, le moteur SQL choisit de parcourir l’index en cluster de RESULT_SPEC (appelé PK_RESULT_SPEC_ZAP)
pour chaque ligne trouvée depuis l’index non-cluster de RESULT appelé RESULT_XI) . Chaque enregistrement trouvé dans l’index non-cluster est recherché dans la table par l’index en cluster jusqu’à ce que la recherche mène au bon résultat de la requête.

La présence de l’opérateur Key lookup indique que le moteur SQL a trouvé un index pour traiter les informations de la requête, mais qu’il a besoin de colonnes supplémentaires pour retrouver les informations nécessaires. Concrètement : le moteur SQL consacre son temps à analyser des données non-clés jusqu’à ce qu’il trouve celles recherchées (en comparant les données de l’index choisi avec celles recherchées, puis en recherchant les informations des colonnes des données à partir des pages de données de la table traitée). Cet opérateur montre qu’à cet étape du traitement de la requête, la table traitée est peu ou mal indexée, du fait de l’absence d’un index composite (couvrant ou include) permettant de gagner du temps en couvrant les colonnes recherchées (ici, REPORTED_VALUE et PRODUCT_NAME).

Notons que la présence d’un opérateur de jointure tel que Hash Join peut indiquer un manque d’index adéquat sur l’une des colonnes concernées par une jointure au sein d’une requête.

Un Hash Join est particulièrement coûteux dans la mesure où il force l’optimiseur SQL à travailler en 2 phases durant la jointure de tables :

  • Phase de construction : une table de hachage sera créée en RAM (dans la mesure du possible) à partir du scan de chaque ligne de la plus petite table concernée par la jointure via l’application d’un algorithme de hachage qui va générer une clé de hachage pour chaque ligne insérée.
  • Phase d’exploration : la totalité de l’autre table (la plus volumineuse) à joindre sera explorée et traitée par l’algorithme de hachage qui va générer une clé de hachage pour chaque ligne à comparer avec le contenu de la table de hachage construite en RAM lors de la phase de construction afin d’en établir les correspondances pour la jointure.

Mais toutefois, ce type d’opérateur reste paradoxalement efficace en cas de traitement d’un très gros volume de données au sein d’une table. Par conséquent, il est important de s’assurer (via les statistiques, par exemple) qu’il traite bien d’une table volumineuse car dans le cas échéant, cela laisserait présager un manque d’indexes potentiel.

Statistiques

Présentation élémentaire

La création d’un index s’accompagne de la création de statistiques fournissant des informations sur le remplissage (ou population) des valeurs de la colonne indexée. Les statistiques sont des métadonnées qui permettent au moteur SQL d’être capable d’estimer combien d’enregistrements va retourner une requête comportant une clause de recherche sur cette colonne de sorte qu’il soit en mesure de pouvoir adopter la meilleure stratégie possible concernant le traitement de la requête.

Ainsi, si un index n’est référencé par aucune statistique, ou si ses statistiques ne sont pas à jour (suite à la mise-à-jour des enregistrements, par exemple), le moteur SQL rencontrera des difficultés à traiter rapidement la requête concernée du fait d’informations inexistantes ou obsolètes.

Lors de l’analyse des requêtes les plus lentes, la règle générale est de se focaliser surtout sur les opérateurs contre-performants (Key Lookup,…) et d’utiliser les statistiques afin de connaître leur coût et jusqu’à quel point ils peuvent impacter le déroulement d’une requête. Si le curseur de la souris est placé sur un opérateur du plan d’exécution, il y a la possibilité d’obtenir moultes informations statistiques telles que le nombre estimé d’enregistrements qui seront retournés, le coût de l’opération en I/O, le coût de l’opération en CPU, la taille de l’ensemble des enregistrements qui seront retournés, le coût de l’opération en termes de temps (en pourcentage, avec une valeur comprise entre 0 et 100) passé à utiliser l’opérateur, etc….

Par exemple, si on reprend le plan d’exécution graphique estimé de notre requête concernée par Key Lookup. et qu’on place le curseur de la souris sur l’opérateur Key Lookup, on obtient les informations suivantes :


On peut voir, ci-dessous, qu’à cette étape du traitement de la requête (opérateur Key Lookup), l’optimiseur SQL bénéficie d’informations telles que :

  • Le coût estimé des opérations I/O (Estimated I/O Cost).
  • Le coût estimé au niveau CPU (Estimated CPU Cost).
  • Le nombre estimé d’exécutions (Estimated Number of Executions).
  • Le coût estimé d’utilisation de l’opérateur cible (Estimated Operator Cost).
  • Le coût estimé du sous-arbre (Estimated Subtree Cost). Il s’agit du coût cumulé du travail de l’optimiseur à cette étape du traitement de la requête (i.e., en tenant compte des précédentes étapes). Bien que cet élément peut paraître insignifiant, il permet à l’optimiseur d’évaluer mathématiquement le coût de l’opérateur en question. En d’autres termes, cela représente le temps que l’optimiseur pense que cet opérateur prendra.
  • Le nombre estimé de lignes (Estimated Number of Rows).
  • La taille estimée des lignes (Estimated Row Size).
  • Les lignes d’enregistrements sont-elles ordonnées (Ordered) ? (Oui, dans le cas d’un index en cluster).
  • L’ordre d’utilisation de l’opérateur au sein du plan d’exécution (Node ID).

D’autres informations subsidiaires, en plus de ces statistiques, sont également fournies, à savoir :

  • Object : indique les objets (tables, colonnes, indexes) utilisés à cette étape du traitement de la requête.
  • Output List : donne la portion de la requête utilisée pour afficher des enregistrements (les fameuses colonnes de la clause SELECT).
  • Seek Predicates : donne la portion de la requête utilisée pour filtrer des enregistrements (au sein de la clause WHERE ou même FROM, par exemple).

Un clic-droit sur l’opérateur permet d’accéder à ses propriétés fournissant également les informations statistiques sur l’opérateur. On y trouve également quelques informations supplémentaires telles que :

  • Estimated Rebinds et Estimated Rewinds, qui indiquent le nombre estimé de fois où un opérateur init() est utilisé dans le plan d’exécution.
  • Forced Index, qui est à True si un indicateur de requête est utilisé pour placer un index spécifique au sein de la requête. Cela permet à l’utilisateur de disposer d’un certain contrôle sur les indexes à utiliser, et donc sur la manière dont la requête pourrait être traitée.
  • NoExpandHint, qui est du même principe que Forced Index, mais appliqué aux vues indexes.

    Les statistiques en I/O

Les statistiques en I/O permettent d’avoir une idée des performances en lecture et en écriture. La lecture sur disque étant l’opération la plus coûteuse d’une requête, il est donc important de limiter le nombre de pages physiquement lues, en s’assurant d’avoir assez de RAM pour permettre au moteur SQL de stocker les pages les plus fréquemment accédées dans son cache, et plus important, en limitant le nombre de pages lues par la création d’index ou l’optimisation du code SQL.

 Le détail des statistiques en I/O d’une requête est récupérable à partir de la commande SET STATISTICS IO ON en début de requête.

 Si on active SET STATISTICS IO pour la dernière requête vue plus haut (sur Key Lookup), on obtient l’exemple de résultat suivant :

Table ‘RESULT_SPEC’. Scan count 1, logical reads 126, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Les lectures logiques affichées par SET STATISTICS IO ON indiquent le nombre total de pages lues en mémoire. Ce qui compte sont les nombres de pages lues les plus élevés et il faudra observer les parties du plan d’exécution qui affichent les opérations sur ces tables (Key Lookup, dans notre cas), et envisager alors la possibilité de créer un index si des scans coûteux d’indexes ou des key lookups est constaté.

Un grand nombre de lectures logiques indique que soit il manque des indexes, soit la requête retourne un très grand nombre d’enregistrements.

Notons que l’indicateur read-ahead reads fournit le nombre de pages que l’optimiseur a chargé en mémoire, à partir du disque, au cas où il en aura besoin pour des raisons de performances. Son addition avec physical reads peut être un bon indicateur du nombre de pages lues sur le disque, indicateur dont la valeur peut varier d’une exécution de requête à l’autre. En d’autres termes, il permet de savoir s’il y a eu assez de RAM pour traiter la requête.

Les lob reads sont généralement des indicateurs de performances moins importants. Ils font référence aux objets volumineux (large object binaries)
comme les images, par exemple.

Vous pouvez d’ailleurs utiliser le perfmon pour avoir une idée des (re)compilations par secondes effectuées par le moteur SQL grâce aux compteurs SQLServer :SQL Statistics : SQL Compilations/Sec et SQLServer :SQL Statistics : SQL Recompilations/Sec . Et cela, sachant qu’un trop grand nombre de (re)compilations (>100 par secondes) peut indiquer une surcharge du cache par de trop nombreux plans d’exécution (soit à cause de la multiplication de requêtes ad hoc, soit à cause de requêtes mal écrites dont le plan est difficilement trouvable au sein du cache par l’optimiseur SQL nécessitant ainsi une recompilation, soit l’utilisation de nombreuses requêtes référençant des objets dont la structure est régulièrement changée, etc…).

Pour aller plus loin…

Dans d’autres billets indépendants, nous aborderons quelques exemples plus poussés d’optimisation de requêtes basés sur l’analyse d’un plan d’exécution.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s