SQL Server Profiler

Aujourd’hui, on va discuter d’un outil assez important que tous développeur qui peut être amené à faire du T-SQL (sous SQL Server) devrait connaître.
Bon, je dis ça, mais y a pas si longtemps, je ne le connaissais pas.
Mais c’est un outil ô combien pratique qui peut permettre de surveiller ce qui se passe sur une base de données et aider à résoudre des problèmes de performances. Et, accessoirement, il est plus « humainement » lisible qu’un plan d’exécution (enfin, ça, c’est mon avis…).

Bref, c’est outil, c’est SQL Server Profiler (oui, le titre spolait déjà le billet !).

 

Installation

 
On va commencer par la base : comment l’obtenir.
C’est quand même plus pratique.

Il est disponible en même temps que SSMS lors de l’installation de composant SQL Server (pour moi : 2008 R2).

Ensuite, il sera disponible dans le menu démarrer :

 

Utilisation

 

Au lancement, il affiche une fenêtre d’un magnifique gris.
Pour le rendre réellement utile, il suffit de faire File > New Trace… ou cliquer sur le premier bouton de la barre d’outil.
Il y aura alors la même fenêtre de connexion que pour SSMS.

La fenêtre suivante est importante.
Elle va permettre de sélectionner le template de traces.
Grosso modo, le template, répond à deux questions : « qu’est ce que je surveille ? » et « qu’est ce que j’affiche ? ».
Il y a plusieurs templates de base qui répondent à des buts différents.

Une fois le template sélectionné, il suffit d’appuyer sur « Run ».

Pour voir ce que ça donne, c’est assez simple :

Dans la partie supérieure, on peut voir tout ce qui se passe sur la base de données.
Dans le cas présent, je lance un select (second évènement) puis j’appelle une procédure (quatrième évènement).
La partie inférieure permet de savoir exactement ce qu’il s’est passé, c’est à dire l’instruction qui est exécutée.
Dans le cas de la capture, c’est le select qui a été généré par Entity Framework (c’est marqué dans la colonne ApplicationName mais les alias sont aussi significatifs).

La colonne « Reads » et la colonne « Writes » indiquent le nombre de pages utilisées.
Sachant que une page possède une taille de 8Ko, cela veut dire que mon select a utilisé 2.848Ko dans la mémoire vive du serveur, 5.816 pour ma procédure.
Cette donnée est TRES importante pour optimiser les requêtes.
En effet, la « Duration » est indicative et dépend fortement de l’activité du serveur.
Par contre, le nombre de Reads/Writes seront du même ordre (pour un même volume de données, s’entend) quelque soit le serveur.
Ainsi, c’est ce nombre qu’il convient de réduire le plus possible.
Si une requête commence à consommer 3.000.000 Reads (facile à atteindre quand on a une base avec un peu de données), cela veut dire qu’elle va prendre 22Go dans la RAM du serveur.

Y a pas longtemps, j’ai eu à optimiser une requête à plus de 300.000.000 pages qui prenait donc 2.4To de RAM. Autant dire que c’est quand même le genre de choses qui peu…ralentir…une base de données (voir même la planter assez correctement).
Pour info, la résolution a été de peupler une table temporaire dont la requête servait avant de sous-requête (et qui était donc exécuter pour chaque ligne contre une seule et unique fois après).

Les autres colonnes me servent plus à titre indicatif ou pour filtrer les données (par exemple sur le Hostname, assez pratique quand on travaille à plusieurs).
Pour mettre en place un filtre, il faut tout d’abord arrêter la trace puis aller dans les propriétés de celle-ci (dans la barre d’outil, 5ème icone).
L’onglet « Events Selection », en bas à droite, il y a un bouton « Column Filters… » :

Il suffit ensuite de choisir la colonne sur laquelle filtrer et de paramétrer le filtre :

Ici, je filtre sur le Hostname correspondant au nom de ma machine (donc, je ne suis pas polluer par mes petits camarades de jeu).

 

Conclusion

 

SQL Server Profiler est un outil assez facile à prendre en main (du moins pour l’usage que j’en fais) et pourtant terriblement puissant.
Lorsqu’on lance une procédure stockée, bien paramétré, il est capable de tracer chaque appel réalisé, permettant de voir quelle est la requête qui pose réellement problème.

D’autre part, comme dit plus haut, il ne faut réellement pas se fier aux temps d’exécution (enfin, il ne faut pas non plus qu’ils soient délirant…).
Par exemple, j’avais un traitement qui prenait 5 minutes sur ma machine de développement. En production, ça prend moins de 30 secondes…
Le seul point commun, c’est les IO (Reads).

D’autres part, si l’on ne peut pas avoir accès à SQL Server Profiler, il y a une version dégradée.
Lorsque l’on exécute une requête sous SSMS, il suffit d’ajouter (avant tout autre instruction) :

SET STATISTICS IO ON

Pour la procédure stockée utilisée plus haut, j’aurais donc, dans l’onglet « Messages » :

(809 row(s) affected)
Table 'Order Details'. Nombre d'analyses 809, lectures logiques 1629, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'Employees'. Nombre d'analyses 0, lectures logiques 1618, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'Orders'. Nombre d'analyses 1, lectures logiques 1623, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

Les « Reads » totaux de la procédures stockées seront la sommes des « lectures logiques ».
Donc 4870 dans ce cas (1629 + 1618 + 1623).

Et oui, il y a une grosse différence en l’exécution sous EF et sous SSMS…
Pourtant, c’est la même requête qui est exécutée, au final :

exec [dbo].[Employee Sales by Country] @Beginning_Date='oct  3 1993  5:07:17:713PM',@Ending_Date='oct  3 2013  5:07:17:713PM'

Mais là, je ne sais pas pourquoi (si quelqu’un a une réponse, je suis preneur ^^).

 

Bonus : Templates

 

J’utilise deux templates un peu custom :

• Template SQL Server 2005
• Template SQL Server 2008 R2

Pour les importer, rien de plus simple :