Utiliser le SQL Profiler pour optimiser une base de donnée

Voici une collection d'articles intéressants concernant l'utilisation du profiler SQL.

How to identify SQL Server performance issues, by analyzing Profiler output?
Fichier: AnalyseProfilerOutput.zip (incluant l'article)


Cet article (a l'origine pour SQL2000) indique quel est la configuration de SQL Profiler à utiliser pour faire une monitoring des requêtes demandant un long temps d'exécution ou consommant beaucoup de ressources processeur.
Cet article s'accompagne de stored procedures (cpu_intensive.sql, long_running_queries.sql, procedure_cache_usage.sql, show_errors.sql) analysant le résultat collecté par le Profiler pour identifier les requêtes les plus gourmandes.

Using the SQL Server Profiler
Fichier: ProfilerTips.zip (articles)
Article concernant "les trucs et astuces" pratiques pour l'utilisation du profiler SQL.
Parmi les conseils rencontrés à plusieurs reprise, il a la préservation des ressources SQL sur le serveur de production durant l'opération de profiling.
Pour se faire, il est conseiller:

1. D'utiliser un autre serveur SQL pour récupérer les informations du profiler.
2. Utiliser un autre serveur SQL (ou Workstation) pour collecter les traces.
   La collecte des traces est gourmande en ressource et peu affecter les performance d'un serveur de production.
   

De la lecture de l'article, il ressort visiblement qu'il est utile de monitorer les classes d'évènements suivantes. Ces classes indiquant généralement des "pertes de performances" du moteur SQL:

• Execution Warnings: Pour les serveurs chargés, il est possible qu'un query ait à attendre la libération de ressources pour être exécuté (même une courte période). Avec cette classe d'événement il est possible de savoir si (et combien de temps) des requêtes doivent attendre après les ressources nécessaires. Les valeurs possibles sont "Query Wait" pour indiquer si un query doit souvent attendre. "Query Time-Out" pour indiquer si un query à recu un time-out en attendant les ressources nécessaires. Si ces évènements arrivent de façon régulière, il est alors nécessaire de réduire ma charge sur le serveur (augmenter le Hardware, ré-écrire les requêtes, choisir de meilleurs indexes pour résource le problème, ou une combinaisons de ces actions).
• Hash Warning: cet événement est utilisé pour mesurer les "hash recursions" ou les "hash bails". Un "hash recursion" (event 0) arrive quand le résultat d'un query ne tiens pas entièrement en mémoire, forcant ainsi SQL Serveur a diviser la source/input en plusieurs parties (alors traitées individuellement). Un "hash bail" (event 1) est même encore pire pour les performances. Il arrive lorsque l'opération de hashing atteind le niveau maximum de récurssion (recursion depth), forcant la requête à fonctionner avec un plan d'exécution alternatif... forcement, l'un bien moins optimal. Le "hash bail" est un cauchemar! Ces deux événements peuvent vraiment dégrader les performances. Les options pour corriger ces problèmes sont: avoir des Index Statistics up-to-date, ré-écrire la requête, tester les optimizer hints, ou ajouter de la RAM au serveur.
  Note: Le but du Hash Warning est de prévenir que quelque chose va de travers... mais il peut également fournit un précieux service. Pour avoir des performances optimums, il fait éviter tout type d'opérations de hashage... même celles fonctionnant correctement. Si un "Hash Warning" est identifié, il peut être utile de voir pourquoi une opération de hash a été effectuée par Sql Serveur. Il peut être bien plus utile de fixer ce problème à la source.
• Missing Column Statistics: Indique quel sont les colonnes du query pour lesquelles il manque des statistiques, tel que les "Index Statistics" utilisés par le Query Optimizer pour évaluer le plan d'execution le plus éfficace. Si une ou plusieurs colonnes de statistiques sont manquantes, Le Query Optimizer pourrait ne pas sélectionner le plan d'exécution le plus efficace... causant ainsi une perte de performance. Pour réssoudre ce type de problème, considérez les approches suivantes: Avoir activé l'option "auto create statistics", utiliser l'instruction CREATE STATISTICS pour créer manuellement les statistiques sur les colonnes manquantes, utiliser les outils "Index Tuning Wizard" ou "Database Engine Tuning Advisor" (pour identifier et créer automatiquement les statistiques nécessaires).
• Missing Join Predicate: Indique si le query dispose ou non d'un "join predicate". Si ce n'est pas ne cas, le query optimiser produira un plan d'exécution peu optimisé. Ajouter des "join predicates" au query pour réssoudre ce problème.
• Sort Warnings: Indique qu'une operation de tri ne peu pas prendre entièrement place en mémoire. L'opération de tri doit être divisé en plusieurs étapes pour être exécutée complètement. Les solutions potentielles pour réssoudre ce problème sont: réduire le nombre de records retournés et triés, réduire le nombre de colonnes à trier, éliminer l'opération de tri, ou ajouter de la RAM. 
  

Cet article contient également quelques options d'affinement pour éviter les pertes d'informations de profiling lorsque le serveur de production est vraiment chargé (voir ici).

Quand l'Auto Update Statistic dégrade les performances!
D'une façon générale, il est approprié d'avoir cette option activée... pour ne pas avoir à ce soucier de cela.
Cependant, l'update statistic peut arriver n'importe quand durant la journée... et par forcement au moment le plus opportun (par exemple, sur une table d'audit de plusieurs Go en pleine heure de pointe!).
Dans ce cas, l'update statistic peut devenir un problème... qu'il est possible de détecter avec le profiler. En monitorant l'événement "Auto Stats" il est possible de connaitre la fréquence et la durée des updates statistics.
Solution: désactivé l'option Auto Update Statistic et programmer un update durant les heures creuses.

Modèles de profiling préféfinis
Voici la définition de quelques modèles qui pourraient s'avérer bien utile suivant les circonstances.

• TSQL: Ce  modèle collecte les requêtes Transact-SQL executés sur le serveur SQL dans l'ordre d'exécution. Cel fournit une vue de type "historique" de l'activité du serveur... très utile pour se faire une large idée de ce qui se passe sur le serveur.
  Tip: Ce modèle fonctionne parfaitement avec les applications développés en ADO. Ne pas oublier de filtrer les entrées sur la DB.
  
• TSQL by Duration: Trace les requêtes Transact-SQL exécutées, ainsi que le temps d'exécution. Ce modèle est utile pour identifier les requêtes qui prennent le plus de temps à s'exécuter.
  Tip: Ce modèle fonctionne parfaitement avec les applications développés en ADO. Ne pas oublier de filtrer les entrées sur la DB. Filtrer les évènements sur la durée minimal (duration) permet de mettre en évidence les requêtes les plus lentes.
  
• TSQL For Replay: Capture tous les détails nécessaires pour re-jouer la trace sur le même serveur SQL (ou un autre serveur). Peut être très utile pour disgnostiquer ou débugger les problèmes.
• Tuning: Capture les informations détaillées sur les Stored Procedures SP et requêtes Transact-SQL incluant les informations relatives aux performances tel que les temps d'exéctions.
• Standard: Ce modèle capture une large quantité d'information (incluant les logins et bien d'autres informations).

Monitorer les Table Scan
En soi, un table scan n'est pas une opération critique... cependant, trop de TableScan peuvent causer des problèmes de performances.
Utiliser la configuration suivante pour monitorer les tables scans:
Profiler Events:

• Scan: Started

Profiler Data Columns:

• IndexID (Grouped)
• Event Class
• ApplicationName
• SPID
• Plus any others you find useful
  

Profiler Filters:

• DatabaseName Like: [Database name being profiled]
• Exclude any applications you don't need to track, such as SQL Agent, etc. 
  

Bien que pas trop élégante (mais efficace), cette trace regroupe les information par IndexID.
IndexID reference un IndId de la table SysIndexes, le nom de l'objet peut être extrait de la colonne "Name". Il faut cependant savoir que beaucoup de TableScan concernent les tables systèmes (qui peuvent être ignorées pour focaliser l'attention sur les User Objects). Bien que le nom de la colonne ne soit pas disponible immédiatement, le nom de l'index (ou de la statistique) est visible. NB: Une colonne peut avoir une statistique... même sans index, c'est la raison pour laquelle SysIndex peut référer un index ou une statistique.

Monitorer les Locks Escalation
Fichier: LockEscalation.zip (article)
Il est également possible de monitorer l'escalade de locking sur SQL server.
Lors de la release de SqlServer 2000, Howard Long a remarqué une escalade du système de lock dans des conditions particulières (grande et large table, requête retournant peut un échantillion de quelque milliers de records sur plusieurs millions, pas de clustered index). Les détails sont disponibles dans son article.

Cette escalade pratique pour minimiser les ressources SQL est cependant préjudiciable dans un système OLTP. En effet, si le front-end est assez lent, l'escalade vers un shared lock (pour un query en read-only) empêchera d'autre processus de faire des updates ou d'insérer de nouveaux enregistrement. "Not a good situation!" selon l'auteur.