Datafly.pro

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. A noter qu'il peut aussi exploiter des fichier .XEL d'évênements étendus, avec plus ou moins de succès.
• 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

Si la cible est une instance SQL 2014 ou ultérieur, j'ai constaté que le traitement durait parfois extrêmement longtemps, la faute au nouvel estimateur de cardinalités à partir de cette version. Il existe un paramètre supplémentaire qui permet de forcer l'estimateur pré-2014 si on rencontre ce problème : -T194

Il y a d'ailleurs une série de traceflags de ce type pour modifier le comportement, je vous invite à consulter le fichier d'aide qui est heureusement installé avec l'outil.

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é.

Pour ne pas lancer automatiquement le report viewer à l'issue du traitement, ajouter le paramètre -T18

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, 2017 ou 2019 alors ?

Si on tente de charger une trace SQL 2016 avec la version CU4 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, readtrace teste le niveau de version de SQL Server dans l'entête du fichier TRC : à l'offset 390 (186 en hexa) du fichier .trc est indiquée la version de SQL Server, en hexadécimal. Par exemple : 0D, soit 13 en décimal, ce qui correspond à SQL 2016 (SQL Server build 13.xx).

Bien que ce ne soit pas clairement annoncé, Microsoft a pourtant mis à jour ReadTrace pour prendre en charge les nouvelles versions de SQL Server, mais il fait maintenant partie de l'outil "Database Experimentation Assistant".

• Téléchargez Database Experimentation Assistant à l'adresse suivante : https://www.microsoft.com/en-us/download/details.aspx?id=54090
• Installez DEA. Dans le dossier "Program Files (X86)\Microsoft Corporation\Database Experimentation Assistant\Dependencies\X64", vous trouverez les dernières versions de ReadTrace.
• Faites une copie de sauvegarde de votre ReadTrace existant (dans "Program Files\Microsoft Corporation\RMLUtils")
• Copiez les 4 fichiers de "Program Files (X86)\Microsoft Corporation\Database Experimentation Assistant\Dependencies\X64" vers "Program Files\Microsoft Corporation\RMLUtils"

Vous avez maintenant une version de ReadTrace qui peut traiter les fichiers de SQL Server 2016, 2017 ou 2019.