Tutoriaux

Parfois, il est bon de se faire expliquer les choses de A à Z, afin de mieux comprendre. Donc pour rendre vos projets plus sucrés, suivez la recette !!!

Retour

Optimisation des requêtes grâce aux outils de performance (SQL Server)

Image

Lorsqu'on réalise une application destinée à être utilisée par un grand nombre d'utilisateurs en simultané, il est très souvent important d'optimiser au maximun son code afin d'économiser des ressources au niveau du serveur. Cette optimisation peut se faire en améliorant les requêtes SQL. Ainsi, SQL Server met à disposition un certain nombre d'outils permettant de mesurer la performance de vos requêtes SQL pour les optimiser afin d'obtenir les meilleurs performances possibles.

Les applications s’exécutant dans un environnement SQL Server ont donc des performances liées au temps  d’exécution des différentes requêtes sur la base de données. Or, il faut savoir que la configuration générale du serveur au niveau système (nombre de processeurs, taille de la mémoire…) ne suffit pas toujours à obtenir les meilleures performances. En effet, les sélections, les modifications et les suppressions dans la base de données demandent un certain nombre de traitements, surtout pour les requêtes complexes (jointures, sous requêtes par exemple), dont le temps augmente bien évidemment avec la taille des tables concernées et le nombre de lignes concernées.

Certaines applications faisant des traitements lourds quotidiens ont donc besoin d’avoir des requêtes optimisées. Ainsi, l’exécution d’un script régulièrement de mise à jour sur la base de données  peut parfois durer plusieurs heures, ainsi il est possible grâce aux outils de performance de réduire ce temps et donc de libérer des ressources.

Bien sûr, l’une des manières majeures d’améliorer une requête est de trouver la syntaxe la plus précise. Autrement dit c’est dans le code de la requête ou de l’application que beaucoup de temps peut être gagné. Toutefois, la manière dont la requête est traitée par la base de données influe également grandement au niveau des performances.

Certains peuvent être découragés à l’idée de devoir configurer de nouveaux paramètres dans SQL Server. Enfin, les SGBDR peuvent souvent apparaître comme de véritables « usines à gaz ». Rassurez-vous SQL Server activent et configurent lui-même la majorité des outils de performances optimisant les requêtes.

Notre objectif étant de comprendre en quoi les outils de performance peuvent aider à l’optimisation des requêtes, nous allons voir théoriquement dans un premier temps comment SQL Server s’organise pour présenter le résultat d’une requête et quels sont les principaux facteurs responsables de la qualité de la performance d’une requête. Ensuite, nous verrons les outils mis à disposition dans le SGBDR de Microsoft pour repérer les requêtes ayant des performances médiocres. Enfin, nous verrons plus en détails quelques exemples précis d’optimisation de requêtes.


1. PRINCIPE THEORIQUE ET VOCABULAIRE

1.1 Plan d’exécution

Lorsqu’une requête est envoyée vers SQL Server, il ne l’exécute pas tout de suite. En effet, au préalable, il utilise son optimiseur de requête pour la compiler dans un plan de requête. Ce n’est que par la suite que ce plan est exécuté pour accéder aux données.

Ce plan établit ainsi la manière dont les tables seront parcourues et comment seront utilisées les index afin d’obtenir le résultat demandé de manière optimale. Ainsi, il est parfois possible que l’optimiseur définisse plusieurs plans d’exécution et choisisse le moins coûteux et le plus rapide. En effet, il effectue un certain nombre de choix afin de repérer les index à utiliser, l’ordre d’exécution des requêtes et d’application des contraintes et le choix des meilleurs algorithmes offrant la solution optimale.

Le plan d’exécution est observable de 2 manières :

Via l’interface graphique, il est également possible de cliquer ici :

  • En texte, il suffit de taper l’instruction « SET SHOWPLAN_TEXT ON » ou « SET SHOWPLAN_ALL ON » afin d’obtenir le plan estimé. Pour plus de détails encore (nombre de lignes affectées, nombre d’exécution de chaque opérateur), il faut utiliser la commande « SET STATISTICS PROFILE ON ». Même chose via l’interface graphique de SQL Server 2008 en cliquant ici :

    Puis dans « avancée », il faut cocher la case « SET SHOWPLAN_TEXT » :

    Voici le résultat obtenu avec un exemple utilisant une sous requête :

  • Sous forme de schéma en utilisant la commande « SET STATISTICS XML ON », ou dans le menu « requête » (ou en cliquant droit dans la fenêtre de requête) en cliquant sur « Afficher le plan d’exécution estimé », ou en utilisant le raccourci ctrl+L.

    Voici, pour le même exemple comment se présente ce schéma :

    A noter que des informations supplémentaires peuvent être affichées au survol des différents icônes :

     

    Le plan d’exécution graphique indique le coût de la requête en pourcentage relativement à la série de requête envoyée. Ainsi, ceci permet d’identifier une requête trop coûteuse à améliorer parmi un lot de requêtes ou de procédures stockées enchainées, comme peut par exemple en contenir un batch d’instructions.

 

Ainsi, le plan d’exécution apporte une grande importance au coût d’exécution d’une requête, c’est pourquoi nous allons à présent nous intéressé aux différents facteurs pouvant être amélioré pour réduire le temps d’exécution d’une requête.


1.2 Identification des éléments influant sur le coût d’une requête

Même s’il existe un optimiseur automatique dans SQL Server, celui-ci ne cherche à optimiser que l’exécution de la requête. Ainsi un certain nombre d’autres critères sont à prendre en compte préalablement pour réduire les coûts.

En voici une liste non exhaustive :

 

  • Vitesse du réseau : un réseau lent peut augmenter le temps d’envoi des requêtes et également freiner le retour des résultats.
  • Disponibilité de mémoire vive : bien évidemment plus la RAM est importante, plus les performances peuvent être améliorées.
  • Utilisation des statistiques utiles
  • Présence d’index utiles : la création d’index est un élément primordial pour l’optimisation des requêtes car il permet notamment de réaliser des statistiques qui amélioreront les performances.
  • Présence de vues indexées utiles : cela pouvant permettre de réduire l’appel à de coûteuses jointures.
  • Partitionnement    http://technet.microsoft.com/fr-fr/library/ms177500.aspx
  • Favoriser les traitements en SQL : en effet, s’il s’agit par exemple d’une requête avec une boucle, il est préférable de la faire en SQL afin de réduire le nombre d’échange réseau.
  • Bien évidemment la syntaxe de votre requête est importante, il faut au départ que votre code SQL soit optimisé.

 

En utilisant le moniteur d’activité, on peut observer le travail du processeur et l’utilisation des ressources, mais aussi repérer les requêtes coûteuses.


2. OUTILS D’ANALYSE DE L’EXECUTION DES REQUETES

2.1 Comprendre le plan d’exécution

Ici, nous allons nous intéresser seulement au plan d’exécution affiché en mode graphique puisqu’il s’agit du plus agréable à lire, par conséquent c’est le plus parlant et compréhensif.

Il faut également remarquer que le plan est propre à une instruction, si le traitement est composé de plusieurs instructions, SQL Server crée plusieurs plans d’exécution. Le schéma étant composé d’icônes reliés les uns aux autres par des flèches, il est important de connaître l’opération symbolisée par chacun des icônes. Pour cela, une légende complète des  icônes est disponible ici (http://technet.microsoft.com/fr-fr/library/ms175913.aspx). Ainsi sans aller dans le détail, on peut juste préciser que la couleur des icônes à une signification :

  • Bleu => pour les itérateurs (opérateurs physiques ou logiques)
  • Jaunes => pour les curseurs
  • Vert => pour les éléments de langage

En ce qui concerne l’organisation du schéma, il s’agit d’une arborescence dont les nœuds sont donc les opérations logiques et physiques symbolisées par les icônes. Ainsi, chacun des nœuds est associé à un nœud parent par une flèche. Si 2 nœuds ont le même parent, ils sont placés dans la même colonne. Comme le montre ce schéma avec les parents en rouge et les nœuds enfants en vert. Il met également en valeur qu’un nœud enfant a ce statut relativement un autre nœud qui est son parent. Ainsi un nœud peut être un nœud enfant mais également parent par rapport à un autre nœud.

 

Ce schéma montre aussi que l’épaisseur de la flèche donne une estimation du nombre de lignes qui seront rapportées par chacune des opérations. Ainsi, la flèche rapportant les commandes est plus épaisse que celle triant les clients.

De plus, bien d’autres informations sont fournies sous forme d’info-bulle lors du survol des nœuds par la souris. Il est ainsi possible de connaitre le type d’opération physique et logique, une estimation de la taille en octets de la ligne produite par l’opérateur, du coût des entrée /  sortie, du coût pour l’unité centrale, du coût de l’opération avec son pourcentage de coût au niveau de cette requête et du nombre de lignes générées.


2.2 L‘importance des statistiques

Afin de fournir le meilleur plan d’exécution, SQL Server peut tenir des statistiques.  Ces statistiques permettent au serveur d’ avoir des informations  sur la manière dont sont distribuées les valeurs dans une ou plusieurs colonnes d’une table ou d’une vue indexé. L’optimiseur pouvant ainsi utiliser ces statistiques  pour connaitre la cardinalité, c’est-à-dire le nombre de lignes rapporté en résultat d’une requête . Ainsi, le choix peut être fait entre l’opérateur de recherche d’index et l’opérateur d’analyse  d’index qui est beaucoup plus gourmand en termes de ressources.

Ainsi différentes options peuvent être utilisées pour cela :

  • AUTO_CREATE_STATISTICS permet de faire des statistiques sur les colonnes (actif par défaut).
  • AUTO_UPDATE_STATISTICS sert à mettre à jour les statistiques quand une insertion, modification ou suppression est faite (actif par défaut).
  • AUTO_UPDATE_STATISTICS_ASYNC permet la mise à jour des statistiques de manière asynchrome.

Pour vérifier que des statistiques sont bien créées par défaut, il est possible de faire un clic droit sur le nom de la base dans l’explorateur d’objets et de choisir « Propriétés ». Ensuite dans « options », il faut vérifier que création des statistiques est bien à la valeur « True ». Même chose pour les 2 types de mise à jour.


2.3 L‘utilisation de repère de plan

Un autre moyen d’optimiser une requête est la possibilité d’utiliser un repère de plan. En effet, la création d’un repère de plan permet d’attacher des indicateurs de requête ou un plan fixe de requête à une requête. Ainsi, lors de l’exécution, un appel sera fait à l’élément attaché afin d’améliorer les performances.

Pour créer un repère de plan, sélectionnez votre base dans l’explorateur d’objets. Puis dans « programmabilité », il faut cliquer droit sur « Repères de plan » et ensuite sur « nouveau repère de plan ».

Dans la fenêtre venant de s’ouvrir, entrez un nom pour le repère. Dans le champ « Instruction », il faut entrer la requête pour laquelle on souhaite appliquer un repère de plan. Choississez « SQL » comme « Type de portée » et enfin il faut insérer une clause « OPTION » dans le champ « Indicateurs » de la forme suivante :

                OPTION (TABLE HINT(nom_de_la_base.nom_de_la_table, indicateur_de_requete_voulu))

Par exemple, pour que l’optimiseur utilise une opération de recherche d’index dans la table spécifiée, il faut utiliser l’indicateur de requête FORCESEEK.


3. EXEMPLES D'OPTIMISATION

Un certain nombre d’articles disponibles en ligne permettent d’aller plus loin en mettant en avant des cas précis d’optimisation de requête. Voici quelques exemples :


Conclusion

Pour conclure, SQL Server est un système de gestion de base de données très soucieux des performances. Ces algorithmes de recherche  permettent déjà de réduire sans aucun réglage les coûts. Toutefois, un certain nombre d’outils d’analyse et d’options de configuration sont disponibles pour permettre une meilleure performance du serveur.

Ainsi, le développeur peut repérer les « goulets d’étranglement »  présent dans les requêtes et tester différentes techniques afin d’accélérer le temps de réponse nécessaire à la réalisation de la requête. 



Vous aimez ce post, partagez-le !! Ou laissez-moi un commentaire !!
Commentez

Articles dans la même catégorie :

Commentaires

revenir au debut de l'article
Retour