Télécharger et installer

Tout d'abord, la dernière version des outils RML (CU4) peuvent être téléchargés gratuitement sur http://www.microsoft.com/en-us/download/details.aspx?id=4511

Les autres pré-requis sont : .NET Framework 4.0/4.5, une installation valide du client SQL Server, et le Report Viewer 2008 Redistributable SP1

RML Utilities comporte les outils suivants :

  • ReadTrace, dont nous parlons ici, permet de charger et pré-traiter les fichiers trc du profiler dans une bases de données
  • Reporter, qui l'accompagne, permet d'en produire un rapport graphique
  • OStress, qui permet de rejouer de l'activité (replay) pour des tests de stress
  • ORCA, agent de replay qui permet de rejouer une activité multi-sessions et gère le suivi des sessions et le sequencing

Présentation de readtrace

Si vous avez déjà fait une capture d'activité sur un serveur en activité, vous avez pu constater que le volume de données peut être gigantesque : 20 Go ou plus pour une heure...
Avec un volume pareil, ce n'est pas possible d'ouvrir ces fichiers avec le profiler : le stockage en base de données (SQL Server) va venir à notre secours.

Nous parlons, bien entendu ici de traces capturées côté serveur et initiées par des commandes SQL (sp_trace_create, sp_trace_setevent, sp_trace_setfilter) qui génèrent des fichiers .TRC. Avec l'installation des RML Utilities, vous trouverez dans le fichier samples.cab plusieurs scripts SQL permettant de configurer les captures avec des niveaux de détail différents (TraceCaptureDef.sql, TraceCaptureDef_ReportMin.sql, TraceCaptureDef_ReportStmtOnly.sql).

ReadTrace va nous permettre de charger vos fichiers .trc dans une base de données, et surtout de faire un travail de "standardisation" des requêtes capturées : passage en majuscules, suppression des commentaires et des sauts de lignes et espaces parasites, paramétrisation des constantes et variables, ...

La requête suivante :

SELECT TOP (1) [ProjetId], [SujetId] 
FROM [Projet]
WHERE [ProjetId] = @p0 -- lire le projet

devient, une fois standardisée :

SELECT TOP ({##}) PROJETID, SUJETID FROM PROJET WHERE PROJETID = @P#

Cela permet ainsi d'effectuer une analyse consolidée en durée, lectures et écritures (total, minimum, maximum, moyenne), pour des requêtes identiques dont seules les constantes ou la présentation changent d'une exécution à l'autre...

Charger une trace avec readtrace

Le traitement se lance en ligne de commande (la documentation des paramètres est fournie avec l'installation dans un fichier pdf). Voici un exemple simple : 

readtrace" -I"D:\TraceFiles\MyTrace.trc" -oE:\Traces\breakout -SMyServer\MyInstance -dMyReadTraceDatabase
  • -I : fichiers trc. Readtrace prend en automatiquement en charge les fichiers successifs (rollover)
  • -o : répertoire de travail
  • -S : instance ou créer la base de données
  • -d : nom de la base à créer

Evitez d'utiliser une instance SQL 2014 ou 2016 comme cible : avec le nouvel estimateur de cardinalités et ses faiblesses, j'ai constaté que le traitement durait parfois considérablement plus longtemps qu'en version 2012, au point d'y passer la nuit ! Vive le progrès...

Utiliser le reporter

A l'issue du chargement, le rapport, dynamique et cliquable avec plein de sous-rapports affichables dans des onglets, est automatiquement généré et affiché.

Ces rapports offrent une bonne vision synthétique des performances des requêtes (par CPU, durée, lectures, écritures), des top 10 pour ces différentes métriques.
Mais on peut aussi profiter de la base de données pour aller un peu plus loin avec quelques requêtes...!

Interroger directement la base de données

Interroger la base de données rend possible toutes les investigations. Obtenir par exemple, le top 200 des requêtes les plus coûteuses en lecture (basée sur leur consommation maximum) :

--Top_MaxReads
SELECT TOP 200
us.HashID
, us.OrigText
, us.NormText
, gs.DBID
, COUNT(*) AS Nb_executions
, SUM(gs.Duration) / 1000000.00 AS Total_duration_s
, AVG(gs.Duration) / 1000000.00 AS Avg_duration_s
, MIN(gs.Duration) / 1000000.00 AS Min_duration_s
, MAX(gs.Duration) / 1000000.00 AS Max_duration_s
, (MAX(gs.Duration)-MIN(gs.Duration))/ 1000000.00 AS Diff_duration_s
, cast(STDEVP(gs.Duration) as bigint) AS Sd_duration
, SUM(gs.Reads) AS Total_reads
, AVG(gs.Reads) AS Avg_reads
, MAX(gs.Reads) AS Max_reads
, MIN(gs.Reads) AS Min_reads
, (MAX(gs.Reads)-MIN(gs.Reads)) AS Diff_reads
, CAST(STDEVP(gs.Reads) AS BIGINT) AS Sd_reads
FROM ReadTrace.tblUniqueStatements us
JOIN ReadTrace.tblStatements gs ON us.HashID = gs.HashID
JOIN ReadTrace.tblBatches tb ON gs.BatchSeq = tb.BatchSeq
GROUP BY us.[HashID]
, us.OrigText
, us.NormText
, gs.DBID
ORDER BY MAX(gs.Reads) DESC;

Compléter l'analyse avec la BI

Et après quelques requêtes SQL supplémentaires, avec un peu d'Excel, de PowerView (ou même de PowerBI) et d'effort, tout est possible. Mieux que le profiler, non ?

- Analyser les durées des requêtes par nombre d'exécutions :

- Suivre les durées d'exécutions d'une requête dans le temps : 

Et SQL Server 2016, alors ?

Si on tente de charger une trace SQL 2016 avec la version actuelle de RML, cela ne fonctionne pas. En fouillant un peu dans la bordée de messages d'erreurs, on trouve l'explication :

[0X00000594] The major version number (13) in the trace file header is not a supported file version.

En fait, c'est simple : à l'offset 390 (186 en hexa) du fichier .trc est indiquée la version de SQL Server, en hexadécimal : 0D, soit 13 en décimal, ce qui correspond à SQL 2016.

Il suffit donc d'ouvrir notre fichier trc dans un éditeur hexa, de remplacer cette valeur par 0C (=12, SQL 2014) et le fichier peut être avalé sans problème !
Pour traiter un grand nombre de fichiers, un petit script powershell devrait pouvoir faire l'affaire...
Mais n'oublions pas que les formats des traces peuvent évoluer d'une version à l'autre, que les traces seront un jour remplacées définitivement par les évènements étendus, et que les RML Utilities (et encore moins cette astuce) ne font l'objet d'aucun support de Microsoft...