Comprendre les plans de requête

  • 11 minutes

Avant d’approfondir les plans d’exécution, vous devez comprendre les bases du fonctionnent des optimiseurs de base de données. SQL Server utilise ce que l’on appelle l’optimiseur de requête basé sur les coûts. L’optimiseur de requête calcule le coût de plusieurs plans possibles en fonction des statistiques dont il dispose sur les colonnes utilisées, ainsi que les index qui peuvent être utilisés pour chaque opération de chaque plan de requête. Sur la base de ces informations, il fournit un coût total pour chaque plan. Certaines requêtes complexes disposent de milliers de plans d’exécution possibles. L’optimiseur n’évalue pas tous les plans possibles, mais il utilise des heuristiques pour déterminer les plans qui sont susceptibles d’être performants. L’optimiseur choisit ensuite le plan de coût le moins onéreux parmi tous les plans évalués pour une requête donnée.

Étant donné que l’optimiseur de requête est basé sur les coûts, il est important qu’il dispose d’entrées utiles pour la prise de décision. Les statistiques que SQL Server utilise pour effectuer le suivi de la distribution des données dans les colonnes et les index doivent être tenues à jour, sinon, elles risquent d’entraîner la génération de plans d’exécution non optimaux. SQL Server met automatiquement à jour ses statistiques en fonction des modifications apportées aux données d’une table. Toutefois, des mises à jour plus fréquentes peuvent être nécessaires pour les données qui changent rapidement. Le moteur utilise de nombreux facteurs lors de la création d’un plan, notamment le niveau de compatibilité de la base de données, les estimations de ligne basées sur les statistiques et les index disponibles.

Lorsqu’un utilisateur envoie une requête au moteur de base de données, le processus suivant a lieu :

  1. La requête est analysée en vue de vérifier sa syntaxe. Si celle-ci est correcte, une arborescence d’analyse des objets de base de données est générée.
  2. L’arborescence d’analyse de l’étape 1 est utilisée comme entrée pour un composant du moteur de base de données appelé Algebrizer, à des fins de liaison. Cette étape permet de vérifier que les colonnes et les objets de la requête existent, et permet d’identifier les types de données qui sont traités pour une requête donnée. Cette étape génère une arborescence du processeur de requêtes, qui se trouve dans l’entrée de l’étape 3.
  3. Étant donné que l’optimisation des requêtes est un processus relativement coûteux en consommation du processeur, le moteur de base de données met en cache les plans d’exécution dans une zone spéciale de la mémoire, appelée « cache du plan ». Si un plan existe déjà pour une requête donnée, il sera récupéré à partir du cache. Une valeur de hachage est générée pour chacune des requêtes dont les plans sont stockés dans le cache. Cette valeur est basée sur le code T-SQL que contiennent les requêtes. Cette valeur est appelée query_hash. Le moteur génère un query_hash pour la requête actuelle, puis vérifier s’il correspond à des requêtes existantes dans le cache du plan.
  4. Si le plan n’existe pas, l’optimiseur de requête utilise alors son optimiseur basé sur les coûts pour générer plusieurs options de plan d’exécution en fonction des statistiques relatives aux colonnes, aux tables et aux index qui sont utilisés dans la requête, comme décrit ci-dessus. Le résultat de cette étape est un plan d’exécution de requête.
  5. La requête est alors exécutée à l’aide d’un plan d’exécution qui est tiré (pull) du cache du plan ou d’un nouveau plan généré à l’étape 4. La sortie de cette étape correspond aux résultats de votre requête.

Notes

Pour en savoir plus sur le fonctionnement du processeur de requêtes, consultez le Guide de l’architecture de traitement des requêtes

Examinons un exemple. Considérez la requête suivante :

SELECT orderdate,
        AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;

Dans cet exemple, SQL Server recherche l’existence des colonnes OrderDate, ShipDate et SalesAmount dans la table FactResellerSales. Si ces colonnes existent, il génère une valeur de hachage pour la requête, puis recherche une valeur de hachage correspondante dans le cache du plan. S’il existe un plan pour une requête avec un hachage correspondant, le moteur essaiera de réutiliser ce plan. S’il n’y a pas de plan avec un hachage correspondant, il examinera les statistiques disponibles sur les colonnes OrderDate et ShipDate. La clause WHERE qui référence la colonne ShipDate est ce que l’on appelle le prédicat dans cette requête. S’il existe un index non-cluster qui comprend la colonne ShipDate, SQL Server l’inclura probablement dans le plan, si les coûts sont inférieurs à ceux qu’implique la récupération de données à partir de l’index cluster. L’optimiseur choisira alors le plan de coût le moins onéreux parmi ceux disponibles et exécutera la requête.

Les plans de requête combinent une série d’opérateurs de relation pour récupérer les données, et capturent également des informations sur les données, comme le nombre de lignes estimé. Parmi les éléments d’un plan d’exécution, il y a également la mémoire nécessaire pour effectuer des opérations comme la jonction ou le tri des données. La mémoire dont a besoin la requête est appelée « allocation de mémoire ». L’allocation de mémoire montre bien l’importance des statistiques. Si SQL Server pense qu’un opérateur va retourner 10 millions de lignes alors qu’il en retourne seulement 100, une quantité de mémoire beaucoup plus importante que nécessaire est allouée à la requête. Une allocation de mémoire plus importante que nécessaire peut provoquer un double problème. Tout d’abord, la requête peut rencontrer une attente RESOURCE_SEMAPHORE, ce qui indique que la requête attend que SQL Server lui alloue une grande quantité de mémoire. Par défaut, SQL Server attend 25 fois le coût de la requête (en secondes) avant de lancer l’exécution, dans une limite maximale de 24 heures. Ensuite, lorsque la requête est exécutée, si la mémoire disponible est insuffisante, la requête se déversera dans tempdb, ce qui est beaucoup plus lent que d’utiliser la mémoire.

Le plan d’exécution stocke également d’autres métadonnées sur la requête, notamment le niveau de compatibilité de la base de données, le degré de parallélisme de la requête ou les paramètres fournis si la requête est paramétrable.

Les plans de requête peuvent être affichés sous forme graphique ou sous forme de texte. Les options basées sur du texte sont appelées à l’aide de commandes SET, et s’appliquent uniquement à la connexion actuelle. Les plans basés sur du texte peuvent être consultés partout où il est possible d’exécuter des requêtes T-SQL.

La plupart des administrateurs de bases de données préfèrent examiner les plans sous forme graphique, car cela leur permet de voir facilement le plan dans son ensemble, notamment ce que l’on appelle la forme du plan. Il existe plusieurs façons d’afficher et d’enregistrer des plans de requêtes graphiques. L’outil le plus couramment utilisé à cette fin est SQL Server Management Studio. Toutefois, les plans estimés peuvent également être affichés dans Azure Data Studio. Il existe également des outils tiers qui prennent en charge l’affichage des plans d’exécution graphiques.

Vous pouvez afficher trois types de plans d’exécution différents.

Plan d’exécution estimé

Ce type est le plan d’exécution généré par l’optimiseur de requête. Les métadonnées et la taille de l’allocation de mémoire de requête sont basées sur des estimations de statistiques, basées sur celles qui se trouvaient dans la base de données au moment de la compilation de la requête. Pour afficher un plan estimé basé sur du texte, exécutez la commande SET SHOWPLAN_ALL ON avant d’exécuter la requête. Lorsque vous exécuterez la requête, vous verrez les étapes du plan d’exécution. Toutefois, la requête ne sera pas exécutée et vous ne verrez aucun résultat. L’option SET reste appliquée jusqu’à ce que vous la désactiviez (OFF).

Plan d’exécution réel

Ce type est le même plan que le plan estimé. Toutefois, ce plan contient également le contexte d’exécution de la requête, notamment le nombre de lignes estimé et le nombre de lignes réel, les avertissements d’exécution, le degré réel de parallélisme (nombre de processeurs utilisés), ainsi que les temps de processeur écoulés utilisés lors de l’exécution. Pour afficher un plan réel basé sur du texte, exécutez la commande SET STATISTICS PROFILE ON avant d’exécuter la requête. La requête s’exécutera, et vous obtiendrez le plan et les résultats.

Statistiques des requêtes dynamiques

Cette option d’affichage du plan combine les plans estimé et réel dans un plan animé qui montre la progression de l’exécution via les opérateurs dans le plan. L’affichage est actualisé toutes les secondes et indique le nombre réel de lignes qui transitent par les opérateurs. Les statistiques des requêtes en direct présentent l’avantage d’afficher le passage d’un opérateur à l’autre, ce qui peut être utile pour résoudre certains problèmes de performances. Ce type de plan étant animé, il n’est disponible qu’en tant que plan graphique.