[SQL Server] Query optimization : concepts généraux

Ce billet offre une petite présentation de quelques concepts liés à l’optimisation de requêtes.

Présentation des niveaux de traitements d’une requête

Quand une requête est lancée au sein de SQL Server, un certain nombre de processus sont mobilisés pour traiter la traiter. Il existe principalement 2 types de processus :

  • Processus qui travaillent au niveau du moteur relationnel SQL à A ce niveau-là, la requête est parsée par un algébriseur, compilée, puis traitée par l’optimiseur de requêtes qui va générer un plan d’exécution. Plan qui, ensuite, sera envoyé au moteur de stockage.
  • Processus qui travaillent au niveau du moteur de stockage à A ce niveau-là, les données sous-jacentes seront lues puis, soit mises à jour, soit affichées pour consultation. Les verrouillages, les opérations de maintenance d’indexes et les transactions sont réalisées au sein du moteur de stockage.

Les différentes phases de traitements d’une requête

La phase de parsing

Quand une requête est soumise à l’optimiseur, elle est d’abord traitée par un processus qui s’assure qu’elle est syntaxiquement bien écrite. On parle de parsing.

La sortie du processus de parsing correspond à un arbre de parsing ou de requête (appelé parse tree, query tree ou sequence tree). Il s’agit d’une représentation logique d’étapes nécessaires à l’exécution de la requête soumise.

Dans le cas d’une requête DML (Data Manipulation Language), le parse tree utilise un mécanisme qui résout tous les noms de tous les objets (tables, colonnes,…) référencés au sein de ladite requête, ainsi que tous les types des colonnes utilisées (INT, VARCHAR,…). Ce mécanisme est appelé « algébriseur » (ou algebrizer, ou normalizer avant SQL Server 2005).

Un processus appelé aggregate binding (ou localiseur d’agrégats) est utilisé pour localiser et analyser les agrégats (ORDER BY, GROUP BY,…) au sein de la requête.

L’algébriseur est telle qu’il permet également de prendre en compte tout alias ou synonymes susceptibles d’être présents dans la requête analysée, et nécessitant d’être pris en compte.

Sitôt la phase de parsing terminée, l’algébriseur génère un arbre de traitement de requête (ou query processor tree) qu’il cède ensuite à l’optimiseur de requêtes qui va passer à la phase de compilation.

A noter que dans le cas d’une requête DDL, l’algébriseur n’est pas utilisé.

La phase de compilation et d’optimisation

L’optimiseur de requêtes de SQL Server est un mécanisme qui contrôle la manière dont le moteur SQL travaille avec une requête. Concrètement, il permet de déterminer la meilleure manière de traiter une requête. Il décide si un index peut être utilisé pour accéder aux données, quel type d’indexes, quelles jointures utiliser, etc…

Les décisions prises par l’optimiseur dépendent de ses calculs associés à un plan d’exécution. On parle là d’optimisation basée sur le coût (ou cost-based optimization).

Lesdits calculs permettent à l’optimiseur d’avoir une idée de la vitesse d’exécution d’une requête. Lors de la phase de compilation de la requête (c’est-à-dire, la mise-en-cache de son plan d’exécution juste avant son exécution), l’optimiseur génère et analyse plusieurs plans d’exécution « candidats », puis opte pour celui qu’il juge le moins coûteux en termes de consommation de ressources I/O et CPU. A moins qu’il réutilise un plan d’exécution déjà en cache.

Le choix se fait en testant les jointures (types, ordres,…), les indexes, etc… Ces tests conduisent à ce qu’on appelle un coût estimé (ou estimated cost).

Il arrive néanmoins que l’optimiseur utilise un plan d’exécution moins efficace s’il estime que le traiter prend moins de temps que les autres plans d’exécution « candidats ». Comme dans le cas d’une requête dépourvue d’index, d’agrégats ou de calculs, où l’optimiseur peut « sous-estimer » l’importance de cette requête en optant pour un plan d’exécution trivial, pour gagner du temps.

Comment l’optimiseur sélectionne-t-il son plan d’exécution dans le cas d’une requête non-triviale ? Il utilise ce qu’on appelle des statistiques, qui sont des métadonnées collectées à partir des colonnes et des indexes au sein d’une base de données. Ces métadonnées fournissent des informations relatives à la distribution des données et quelques-uns de leurs particularités (unicité,…).

Bref, l’optimiseur utilise ces statistiques et détermine à partir d’elles le plan d’exécution qu’il juge meilleur. Et dès qu’il a choisi son plan, il crée ce qu’on appelle un plan d’exécution final (ou actual plan), qu’il met ensuite dans le cache des plans d’exécution (plan cache), sauf si un plan d’exécution identique existe et est destiné à être réutilisé, ce qui veut dire qu’après avoir choisi son plan d’exécution, il le compare avec ceux déjà en cache afin de savoir s’il y a doublon ou non.

Sitôt toute la phase de compilation et d’optimisation terminée, SQL Server se charge de passer à la phase d’exécution.

La phase d’exécution

Dès la fin de la phase de compilation, SQL Server traite la requête sur le moteur de stockage en s’appuyant sur le plan d’exécution préalablement mis en cache, puis retourne le résultat de son
exécution.

En résumé…

Le schéma ci-dessous résume les différentes phases du traitement d’une requête, de son parsing à son exécution en passant par sa compilation et son optimisation.


En résumé :

  • Au sein du moteur relationnel :
    • La phase de parsing permet de vérifier la syntaxe de la requête grâce notamment à un processus appelé « algébriseur » qui va utiliser ce qu’on appelle un parse tree. Un arbre logique appelé query processor tree sera généré en sortie, c’est-à-dire à la fin de la phase de parsing.
    • La compilation et l’optimisation de la requête démarrera à l’aide du query processor tree. L’optimiseur va procéder au choix (basé sur des calculs de coûts via des statistiques et tests) du plan d’exécution le plus adapté à la requête, puis le mettre en cache s’il est unique ou l’ignorer s’il existe un doublon dans le plan cache. Sitôt toute cette phase terminée, le plan d’exécution (estimé et ainsi que celui réel) sera soumis au moteur de stockage de SQL Server.
  • Au sein du moteur de stockage :
    • En tenant compte des informations du plan d’exécution soumis par l’optimiseur, la requête sera exécutée, puis les résultats affichés.

Pour aller plus loin…

Quelques cas d’optimisation de requêtes seront abordés dans des billets indépendants, ainsi qu’une présentation plus détaillée des concepts liés aux plans 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