Les événements étendus

Chapitre 05.03

  11 minutes à lire  

SQL Server 2008 inclut une nouvelle architecture de gestion d’événements, appelées événements étendus (extended events, ou XEvents), basés sur l’architecture de Event Tracing for Windows (ETW), qui permet de définir des événements récupérables par plusieurs clients, de façon synchrone ou asynchrone[^17]. Au moment où nous rédigeons ce livre, SQL Server 2008 est toujours en version beta et tous les événements ne sont pas implémentés. La puissance de XEvents est bien supérieure aux méthodes de trace actuelle. Nous allons en parcourir les fonctionnalités.

Architecture

Les fournisseurs de ces événements sont organisés en packages, de simples containers, comme des espaces de noms, identifiés par un nom et un GUID, qui contiennent divers objets :

  • events – des événements déclenchés, ils correspondent

    principalement aux compteurs de performance SQL Server et aux événements de trace SQL ;

  • actions – des actions attribuées dynamiquement au déclenchement

    d’un événement, elles peuvent êtres utiles pour ajouter des informations supplémentaires aux événements( par exemple le SPID, le code SQL de l’instruction, etc.) ;

  • targets – des « consommateurs » d’événements. SQL Server en

    implémentent plusieurs, soit pour écrire les événements dans un fichier, soit pour les traiter en mémoire (accessible par des vues de gestion dynamique). Par exemple, la destination « Event pairing » matche les couples d’événements, et permet d’identifier des événements non terminés (transaction non validée, verrou non libéré, etc.) ;

  • types – indiquent les formats d’événement pour assurer leur

    prise en main dans le processus de déclenchement (notamment leur mise en buffer) ;

  • predicates – des clauses de filtre. Le filtrage se fait au plus

    près de l’événement, ce qui évite de déclencher l’événement s’il ne correspond pas au filtre. Les prédicats peuvent conserver leur état, on peut donc par exemple compter les déclenchements d’un événement dans un prédicat, et utiliser ce compteur comme filtre ;

  • maps – des tableaux de référence qui permettent de lier des

    identifiants courts dans l’événement, à des correspondances claires dans la map. Cela permet, comme dans un modèle relationnel, de diminuer la taille des données d’événement.

Les événements sont classés en « channels » (sur le modèle de ETW). Les événements faisant partie du channel « Analytic » sont ceux qui seront principalement intéressants pour l’analyse de performances. Ils contiennent des événements correspondant aux traces SQL et aux compteurs de performances fournis par SQL Server (avec beaucoup plus d’informations qu’une simple valeur de compteur). Les colonnes keyword et channel de chaque événement permettent d’en retrouver le package et le channel, et de les classer. La colonne keyword correspond à une information claire tirée d’un lien sur une map.

SELECT map_value as Keyword

FROM sys.dm_xe_map_values

WHERE name = ‘keyword_map’;

Vous trouvez une requête vous permettant de lister les événements disponibles dans l’entrée des BOL SQL Server 2008 nommée « How to: View the Events for Registered Packages ».

Le coût de déclenchement des événements étendus a été optimisé autant que possible, et est léger (Microsoft déclare 2 microsecondes de processeur cadencé à 2GHz). Les événements asynchrones sont à préférer, car ils ont un impact plus faible sur les performances du serveur. Les événements asynchrones sont gérés par des buffers, des parties de mémoire qui stockent les événements avant de les envoyer aux clients qui en font la demande ou de les écrire sur le disque. Cette deuxième partie sera effectué par un thread en background, ce qui est moins coûteux.

Créer une session

Pour récupérer des événements étendus, vous devez d’abord créer une session, qui est la déclaration nommée d’un groupe d’événements tracés. Ces sessions étant persistées dans des tables systèmes, elles existent donc encore après un redémarrage de l’instance, et elles peuvent être configurées pour redémarrer automatiquement avec elle.

Un session se crée avec la commande CREATE EVENT SESSION, dans laquelle nous indiquons les événements à récupérer (ADD EVENT nom_du_package.evenement), les destinataires (ADD TARGET), etc. :

CREATE EVENT SESSION masession

ON SERVER

ADD EVENT sqlos.async_io_requested

ADD EVENT sqlos.async_io_completed

ADD EVENT sqlserver.database_transaction_begin

ADD EVENT sqlserver.database_transaction_end

ADD TARGET package0.etw_classic_sync_target

(SET default_etw_session_logfile_path = N’C:\temp\sqletw.etl’);

Pour démarre la session :

CREATE EVENT SESSION masession

ON SERVER

STATE = start;

Vous ne pouvez créer la session et la démarrer en même temps, un peu comme avec les procédures stockées de définition de SQL Trace, simplement parce que les deux opérations ne peuvent être transactionnelles ensemble : on ne peut pas annuler la création des métadonnées et l’envoi des événements en même temps, puisqu’on ne peut « désenvoyer » les événements.

Les sessions peuvent être requêtées dans les vues systèmes suivantes :

  • sys.server_event_sessions

  • sys.server_event_session_actions

  • sys.server_event_session_events

  • sys.server_event_session_fields

  • sys.server_event_session_targets

Pour améliorer les performances de la session, permettez le vidage de buffers en cas de manque, ceci en activant l’option ALLOW_SINGLE_EVENT_LOSS, ou ALLOW_MULTIPLE_EVENT_LOSS. Le nombre d’événements perdus est indiqué avec l’option ALLOW_SINGLE_EVENT_LOSS. Par contre, ALLOW_MULTIPLE_EVENT_LOSS libère la totalité d’un buffer, qui peut contenir un grand nombre d’événements, et rend impossible le retour du nombre d’événements perdus. Dans la plupart des cas, l’option ALLOW_SINGLE_EVENT_LOSS est recommandée, car elle offre de bonnes conditions de performances.

NO_EVENT_LOSS : Certains événements bas niveau ne peuvent être paramétrés avec NO_EVENT_LOSS, car l’attente sur la libération de buffer serait trop handicapant pour le système. C’est le cas d’événements comme FILE_IO ou CONTEXT_SWITCH. Dans la grand majorité des cas, ALLOW_SINGLE_EVENT_LOSS est parfaitement acceptable pour récupérer des informations utiles.

Tout l’architecture des événements étendus est visible à travers des vues de gestion dynamique préfixées par sys.dm_xe_.

La vraie question, au moment où nous écrivons ce livre, est : que faire des événements déclenchés ? Deux solutions :

  • Enregistrer le résultat de la session dans un fichier .etl, à

    l’aide du destinaire etw_classic_sync_target comme nous l’avons vu dans notre exemple de code. Ce fichier .etl est en format binaire, il est importable directement dans les outils xperf (voir ci-dessous, section 5.4) sur Vista ou Windows Server 2008. En Windows Server 2003, l’exécutable tracerpt.exe (installé avec Windows, dans system32), vous permet de transformer le contenu du fichier en format CSV, qui peut être ensuite être importé dans votre outil de supervision.

  • Récupérer les événements directement dans la vue de gestion

    dynamique sys.dm_xe_session_targets, à l’aide des destinataires asynchronous_bucketizer (écriture asynchrone dans un cache mémoire) et pair_matching. Vous trouverez un exemple de destinataire pair_matching, qui permet de tracer des événements liés, comme les blocages de processus, dans les BOL, sous l’entrée « How to: Determine Which Queries Are Holding Locks ».

5.4 Les outils de supervision

Un certain nombre d’outils, qu’ils soient livrés par Microsoft ou non, sont utiles pour tracer les performances ou des informations précises du système. Dans cette section, nous allons rapidement en lister quelques uns, afin de vous encourager à les découvrir et à les utiliser.

Windows Performance Toolkit

Le Windows Performance Toolkit, aussi appelé Xperf, est un outil disponible pour Vista et Windows Server 2008, composé principalement de deux exécutables : xperf.exe, qui permet des captures de trace, et xperfview.exe, qui les affiche. xperf.exe est basé sur ETW (Event Tracing for Windows), que vous pouvez activer dans le traditionnel moniteur de performances (perfmon) via le nœud Performance Log and Events / Trace Logs. Son avantage est de pouvoir corréler des traces de format ETW, par exemples des traces du système, et des traces des événements étendus de SQL Server 2008.

Il est téléchargeable sur le site de Microsoft à cette adresse : http://www.microsoft.com/whdc/system/sysperf/perftools.mspx. L’enregistrement de trace est possible sur Windows Server 2003 en copiant à la main l’exécutable xperf.exe, mais le processing du résultat de la trace n’est possible que sur Vista ou Windows Server 2008.

SQLDIAG

SQLDIAG est un exécutable installé avec SQL Server, dont but est de collecter le plus possible d’informations du système, à la base pour les envoyer aux équipes support de Microsoft. Il récupère des données du système et de SQL Server, à la demande (prenant un snapshot) ou de façon continue. Il peut être invoqué en ligne de commande, ou installé comme un service. Il est capable de récupérer ses informations d’instances SQL Server en cluster. Il enregistre son résultat dans des fichiers texte dans un répertoire spécifique. Il peut être configuré en ligne de commande, ou par un fichier de configuration. Il peut récupérer des journaux de performance Windows, des traces SQL, les journaux d’événement Windows, les informations de configuration du système et de SQL Server, les informations de blocage de sessions, etc. C’est donc un outil extrêmement intéressant pour le diagnostic de problèmes. Vous trouverez plus d’informations dans les BOL, sous l’entrée « SQLdiag Utility ».

SQLNexus

SQLNexus est un outil développé par Bart Duncan et Ken Henderson, qui simplifie l’utilisation de SQLDIAG, tout d’abord en fournissant un fichier de configuration pour SQLDIAG et des scripts pour obtenir des informations supplémentaires des vues de gestion dynamique, ensuite en stockant les output dans une base de données, et en offrant des rapports Reporting Services très complets pour analyser les résultats. Vous pouvez le télécharger sur CodePlex : http://www.codeplex.com/sqlnexus

Log Parser

Log Parser n’est pas spécifiquement destiné à SQL Server. C’est un outil très puissant de lecture, de recherche et de reporting qui travaille sur tous types de fichiers journaux (fichiers log, XML, CSV) et dans les journaux d’événement de Windows, la base de registre, etc. Vous pouvez lire, chercher avec un langage de requête de type SQL, formatter le résultat en texte ou en HTML, et même générer des graphes. Vous pouvez télécharger Log Parser ici : http://www.microsoft.com/technet/scriptcenter/tools/logparser/. Une interface graphique libre de droits à été créée par un programmeur, vous la trouvez ici : http://www.lizardl.com/.[^18]

Windows sysinternals

Les outils Sysinternals, développés par Mark Russinovich (un expert reconnu des méandres de Windows) et Bryce Cogswell, sont maintenant disponibles sur le site de Microsoft, depuis que Winternals, la société de Russinovich, a été rachetée en 2006. Vous trouverez à cette adresse : http://technet.microsoft.com/en-us/sysinternals/ des outils de diagnostic du système indispensables et simples d’utilisation. Parmi eux :

  • DiskMon – trace l’activité disque ;

  • Process Monitor – affiche en temps réel l’activité des

    processus et threads, de la base de registre et des activités disque ;

  • **Process Explorer **– affiche en détail l’activité des

    processus : handles, DLL utilisées, fichiers ouverts, etc. Très utile pour entrer dans les détails ;

  • TCPView – affiche en temps réel les ports TCP et UDP ouverts.

    Un équivalent graphique de netstat.

SQL Server 2005 Performance Dashboard Reports

Le « SQL Server 2005 Performance Dashboard » est une collection de rapports Reoprting Services requêtant les vues de gestion dynamique. Elle est téléchargable sur le site de Microsoft, sous forme de fichier d’intallation .msi. Après installation, vous disposez des fichiers de définition de rapports .rdl, dans le répertoire choisi à l’installation, ainsi qu’un fichier de script setup.sql, à exécuter avant toute chose. Ce script crée quelques objets dans msdb, sans plus. Tous les rapports sont basés sur des DMVs. Ensuite, pour afficher les rapports dans SSMS, vous pouvez utiliser la fonctionnalité de rendu de rapports locaux (Reporting Services n’est donc pas requis).

Pour ajouter un rapport local, choisissez « Custom Reports » dans le menu affiché en figure 5.22.

Fig. 5.22 – choix des rapports

allez chercher ensuite le rapport performance_dashboard_main.rdl dans le répertoire d’installation, comme montré en figure 5.23.

Fig. 5.23 – rapport performance_dashboard_main

Le rapport s’affiche dans une nouvelle fenêtre, comme illustré en figure 5.24.

Fig. 5.24 – rapport principal

Ce rapport donne des liens sur tous les autres, c’est donc votre point d’entrée sur les informations de charge, les requêtes les plus coûteuses, etc. Une fois choisi, il continuera à apparaître dans le menu « Reports ».

Server Performance Advisor (SPA)

De son nom complet, « Microsoft Windows Server 2003 Performance Advisor », SPA est un outil graphique qui simplifie la collecte de données de performances et offre une vue commentée des performances générales du système, avec des conseils. Vous pouvez le télécharger sur le site de Microsoft, avec une recherche sur son nom complet.

Un autre outil de ce type existe, nommé PAL (Performance Analysis of Logs). Il s’agit d’un script VBScript hébergé sur CodePlex (le serveur de développements communautaires tournant autour des produits Microsoft), qui avale un journal de compteurs (voir section 5.3.2) et produit un résultat en tableau HTML indiquant si les valeurs sont en-deça ou au-delà des limites conseillées par Microsoft. Site de l’outil : http://www.codeplex.com/PAL/.

Management Data Warehouse (SQL Server 2008)

SQL Server 2008 intègre un outil de centralisation des données de performances, appelé « Management Data Warehouse », qui permet, à travers des lots Integration Services créés par assistant, de centraliser des collections données (data collections) de gestion et de performance basées sur les traces, des compteurs de performances, les vues de gestion dynamique, etc. dans un entrepôt centralisé (une base de données), qui peut collecter les données de plusieurs instances SQL. Des rapports Reporting Services locaux sont ensuite disponibles pour consulter ces statistiques. C’est un outil intéressant de surveillance centralisée de vos instances SQL..

deuxième partie

  1. Optimisation des requêtes…

Le travail d’optimisation sur un serveur de production est avant tout un travail de modélisation logique et physique des données et d’amélioration de la qualité du code SQL. Même les machines les plus puissantes peinent à exécuter des requêtes mal écrites, et doivent attendre lorsque des verrous sont posés sur les données désirées. C’est au niveau du code que les problèmes se posent en général, et c’est là où l’effet de levier est le plus important. Ce n’est que lorsqu’on a la garantie que le code et le schéma sont optimisés, que la mise à jour matérielle doit être envisagée. Nous allons donc passer du temps sur ce sujet essentiel.