[SQL] Query Performance Tuning : Start to Finish

Cette session a pour but de comprendre pourquoi certaines requêtes SQL sont lentes afin les optimiser.

Speaker : Grant FRICTHEY MVP SQL Server

On commence par une introduction sur le pourquoi de la lenteur de certaines requêtes SQL ? Parce qu’elles sont mal pensées, parce que la volumétrie a changé, … et sur les prérequis permettant de monitorer correctement les requêtes (bonne connaissance des DMVs, comprendre un plan d’exécution, etc.)

  1. 1.       Monitorer les requêtes SQL

–          Pour monitorer correctement nos requêtes, il est important d’établir et de maintenir une « baseline » contenant un certain nombre d’indicateurs (Hardware, OS, SQL Server) permettant de mesurer l’état de nos requêtes et d’intervenir bien en amont en cas de problème.

–          Il existe plusieurs outils permettant de mettre en place une « baseline » :

  • « Performance Monitor » : c’est l’outil principal même s’il n’est pas pratique à maintenir
    • Première démonstration du « Performance Monitor », on crée un « template » avec un compteur que l’on va planifier quotidiennement
    • « Dynamic Management Objects » : objets que l’on peut attaquer à partir de requête T-SQL dans management studio)
    • « SQL Data Collection » : permet immédiatement d’avoir des statistiques sous forme de rapports)
    • Third Party Tools

–          Les causes d’une requête peu performante peuvent être diverses (mémoires, E/S disque, processeur, réseau, etc.) et à chaque cause on trouve ces compteurs (par exemple pour les problèmes de mémoires on trouve les compteurs « Pages/sec », « Page Life Expectancy », etc.)

  • Démonstrations sur l’utilisation des DMOs les plus pertinentes permettant de retourner à l’instant t la valeur de certains compteurs
    • Memoire à “sys.dm_os_ring_buffer”
    • E/S disque à « sys.dm_io_virtual_files_stats » (stats des fichiers de base de données)
    • Processeur à « sys.dm_os_worker »

–          Il existe plusieurs DMOs permettant d’analyser à un instant t les différentes requêtes lancées sur le serveur

  • « sys.dm_exec_requests »
  • « sys.dm_exec_query_stats »
  • « sys.dm_exec_procedure_stats »
    • Démonstration avec la fonction « sys.dm_exec_query_stats » couplée avec la fonction « sys.dm_exec_query_plan »  permettant notamment de visualiser les plans d’exécution
    • Démonstration d’une requête couplant différents DMOs permettant d’afficher pour chaque requête la durée d’exécution, le plan d’exécution et éventuellement les indexes manquants.
    • Puis une autre requête permettant d’afficher toutes les opérations

–          Le « SQL Profiler » est très intéressant pour analyser les problèmes mais ne jamais l’utiliser en production

  • Démonstration : utilisation du template « Tuning », enregistrement des traces dans un fichier (utiliser l’option « Enable file rollover » afin d’éviter de trop gros fichiers ~20 MB) puis export sous forme d’un script SQL que l’on utilisera dorénavant plutôt que l’interface

–          Les utilitaires RML (gratuits et téléchargeable sur le site de Microsoft) permettent également d’analyser les requêtes.

  1. 2.       Optimiser les requêtes

–          L’optimiseur de requêtes permet d’améliorer les performances de nos requêtes lors de l’exécution. Il n’est pas parfait mais on peut l’aider à le rendre plus efficace. Il se base notamment sur les statistiques.

–          Lorsqu’une requête est exécutée, elle passe d’abord par le moteur relationnel puis par le « Query Parsor » qui vérifie la syntaxe puis dans l’« Algebrizer » qui transforme les objets pour qu’il puisse être interpréter dans l’«Optimizer » qui produit le plan d’exécution.

–          Pour observer l’optimisateur, il existe un DMO

  • « sys.dm_exec_query_optimizer_info »
    • Démonstration montrant que l’« Optimizer » change à chaque fois que l’on lance une requête.

–          Les statistiques sont créées lors de la création d’index, de colonnes ou manuellement. Ils sont mis jour manuellement. Par défaut, les statistiques sont créées et maintenues automatiquement.

–          Pour analyser les statistiques, il existe la commande DBCC SHOW_STATISTICS. On peut, par exemple, analyser la densité des indexes (colonne « density ») et ainsi voir leur pertinence.

–          Pour de meilleurs plans d’exécution, on peut mettre à jour manuellement les statistiques

  • Procédure sp_updatestats
  • UPDATE STATISTICS X [INDEX], il faut faire attention car cette commande sur des grosses tables peut causer des blocages.

–          Il est important de connaître les principaux types d’indexes

  • CLUSTERED INDEX (par défaut sur la clé primaire de la table). Les données sont triées physiquement par la clé.
  • NON-CLUSTERED INDEX. Les données sont triées de manière logique

–          Il existe également 3 types de contraintes.

  • PRIMARY KEY à « Clustered Index » crée par défaut
  • FOREIGN KEY à Pas d’index crée par défaut
  • UNIQUE CONSTRAINT à Il s’agit d’un index

->  En fonction des indexes, des jointures, etc. chaque requête peut avoir de nombreux plans d’exécution

  1. 3.       Lire un plan d’exécution

–          Le plan d’exécution est nécessaire pour de nombreuses raisons

  • Voir les indexes utilisés
  • Voir le type de jointure utilisés
  • Pour l’optimisation, …

–          La plupart des requêtes une fois exécutées vont dans le cache

–          Lorsque l’on lit un plan d’exécution, il est important de regarder un certain nombre de choses comme les « warnings », les opérations qui sont coûteuses, les « scans » de table, les opérations qui n’ont pas lieu d’exister, etc.

  • Démonstration de la lecture d’un plan d’exécution avec la même requête exécuté une première fois sans index puis avec des indexes. On peut voir que la deuxième requête est beaucoup plus efficace.

–          En cliquant sur les propriétés du plan d’exécution, on peut voir un certain nombre d’information comme le niveau d’optimisation de la requête. (Hash Match va dans tempdb)

  1. 4.       Description des problèmes principaux de lenteur de certaines requêtes.

–          Une seule solution. Trouver et réparer le problème

  • Démonstration 1 : Une requête lente alors qu’elle ne retourne que 2 lignes. On analyse le plan d’exécution et on remarque plusieurs soucis résolus en ajoutant des indexes
  • Démonstration 2 : Une requête un peu plus compliqué. Pas besoin d’analyser le plan d’exécution, on voit qu’il y a un problème dans la conception de la requête.

–          La lenteur peut provenir :

  • D’un problème de « reniflement » des paramètres dans une procédure stockée par exemple. En effet, Lorsqu’une procédure stockée est compilée ou recompilée, les valeurs des paramètres sont «reniflées» et utilisées pour l’estimation des cardinalités. Le plan d’exécution est alors optimisé avec ces valeurs de paramètres. Cependant, à un moment donné dans le futur, le moteur doit compiler/recompiler un nouveau plan pour la procédure stockée. Malheureusement, l’exécution de la procédure qui a compilé le nouveau plan avait un nouveau paramètre ce qui entraîne une dégradation des performances.
    • Démonstration avec une procédure stockée compilé avec un paramètre puis recompilé avec un nouveau paramètre. Une solution crée une variable local récupérant le contenu du paramètre ou utilisé le mot clef « OPTIMIZE FOR »
    • D’une mauvaise utilisation des indexes (le « scan » d’une table n’est pas forcement mauvais par exemple).
      • Démonstration avec une requête contenant un index qui répond différent selon que l’on utilise le mot clé « BETWEEN » ou le mot clé « IN » contenant l’ensemble des valeurs du « BETWEEEN »
    • Il est recommandé de ne pas utiliser les « User Defined Functions » lorsque l’on doit retourner beaucoup de lignes
    • Dans le cas de requêtes qui parfois sont lentes, il faut planifier des traces via le « Profiler »

En conclusion, cette session complète bien celle d’hier et permet de mieux comprendre les plans d’exécution ce qui peut, pour certaines requêtes, s’avérer crucial.

Julien

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