Blog

XEvent : Requêtes longues


Lorsque des lenteurs sont détectées sur une instance ou à des fins proactives, il peut être intéressant de consigner ponctuellement les requêtes qui excédent un certain temps d’exécution. Ces requêtes ne sont pas forcément bloquantes mais coûteuses en ressource CPU, I/O et mémoire.

Etape 1

Nous allons créer une arborescence de répertoires sur laquelle le compte de service SQL Server aura le droit d’écrire. Le répertoire long_queries stockera les traces XEvent concernées.

Expert SQL Server - XEvent : Requêtes longues - SQL Server  - xevent_dir

Etape 2

Création de la trace XEvent en spécifiant le nom long_queries et le chemin D:\XEvent\long_queries\long_queries. Notez qu’il y a un rollover de défini sur 5 fichiers par défaut pour une taille limitée à 5 Mo par fichier, les traces n’étant pas supposée saturer les disques. En aucun cas, cette trace XEvent ne doit être activée en permanence car elle peut être particulièrement consommatrice, le filtrage est aussi un impératif (clause WHERE).

– Processus utilisateurs (VS système)
– Durée : 30 000 millisecondes => 30 sec
– Base de données spécifique

Dans la trace ci-définie précédemment, seules les procédures stockées exécutées à distance (VS module_end) et requêtes paramétrables dont la durée globale d’exécution est d’au moins 30 sec, seront présentes. On peut affiner en remontant la ou les instructions ayant une durée d’exécution individuelle d’au moins 30 sec dans une procédure stockée en utilisant l’évènement sp_statement_completed à la place de rpc_completed selon le besoin. Un relevé des requêtes Ad Hoc pourra être réalisé via l’évènement sql_batch_completed.

Voici quelques équivalences entre le profiler et XEvent :

Classe Événement Profiler Événement XEvent
Stored Procedures RPC:Completed rpc_completed
Stored Procedures SP:Completed module_end
Stored Procedures SP:StmtCompleted sp_statement_completed
TSQL SQL:BatchCompleted sql_batch_completed
TSQL SQL:StmtCompleted sql_statement_completed

Etape 3

Démarrage de la trace XEvent long_queries

Consultation des requêtes longues

Le détail des requêtes longues peut être consulté en parsant le XML des traces XEvent générées et le résultat reste à historiser dans une table au besoin. Il est désormais possible d’identifier les requêtes dont le temps d’exécution excède une durée déterminée.

 

Auteur

Expert SQL Server - XEvent : Requêtes longues - SQL Server  - avatar_ninja_tete-150x150
Sarah Béquet
Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.
error: