Démarrage rapide : événements étendus

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Les événements étendus sont une caractéristique légère de monitoring des performances qui permet aux utilisateurs de collecter les données nécessaires à la surveillance et au dépannage des problèmes. En utilisant les événements étendus, vous pouvez voir les détails des opérations internes du moteur de base de données pertinentes à des fins de monitoring des performances et de dépannage des problèmes. Consultez Vue d’ensemble des événements étendus pour en savoir plus.

Cet article vise à aider les administrateurs, les ingénieurs et les développeurs SQL qui découvrent les événements étendus et qui souhaitent commencer à les utiliser et voir les données d’événements en quelques minutes seulement.

Les événements étendus sont également appelés XEvents, ou parfois simplement XE.

Après avoir lu cet article, vous pourrez :

• Créer une session d’événements dans SQL Server Management Studio (SSMS), avec des exemples de captures d’écran
• Corréler les captures d’écran avec les instructions Transact-SQL équivalentes
• Comprendre en détail les termes et les concepts derrière l’interface utilisateur SSMS et les instructions T-SQL XEvents
• Voir comment tester votre session d’événements
• Comprendre les résultats de la session, notamment les éléments suivants :
  • Les options disponibles pour le stockage des résultats.
  • Les résultats traités par rapport aux résultats bruts.
  • Les outils permettant de visualiser les résultats de différentes manières et à différentes échelles de temps.
• Voir comment rechercher et découvrir tous les événements disponibles
• Comprendre les relations entre les vues système d’événements étendus

Conseil

Pour plus d’informations sur les événements étendus dans la base de données Azure SQL, y compris des exemples de code, consultez Événements étendus dans la base de données Azure SQL et Azure SQL Managed Instance.

Prérequis

Pour commencer, vous avez besoin des éléments suivants :

1. Téléchargez SQL Server Management Studio (SSMS). Nous vous recommandons d’utiliser une version récente de SSMS avec les dernières améliorations et correctifs.
2. Assurez-vous que le compte dispose de ALTER ANY EVENT SESSIONl’autorisation de serveur.

Vous trouverez d’autres informations sur la sécurité et les autorisations relatives aux événements étendus à la fin de cet article en annexe.

Événements étendus dans SSMS

SSMS fournit une interface utilisateur (IU) entièrement fonctionnelle pour les événements étendus. De nombreux scénarios peuvent être réalisés à l’aide de cette IU, sans avoir à utiliser T-SQL ou des vues de gestion dynamiques (DMV).

Dans la section suivante, vous pouvez voir les étapes de l’IU qui permettent de créer une session d’événements étendus et voir les données rapportées. Après avoir suivi ou examiné les étapes pratiques de cet article, vous pouvez lire les concepts impliqués dans les étapes pour une compréhension plus approfondie.

Créer une session d’événement dans SSMS

Quand vous créez une session d’événements étendus, vous indiquez au système :

• Les événements qui vous intéressent
• La manière dont vous voulez que le système vous indique les données

La démonstration ouvre la boîte de dialogue Nouvelle session, montre comment utiliser ses quatre pages :

• Général
• Événements
• Stockage des données
• Avancé

Le texte et les captures d’écran qui l’accompagnent peuvent être légèrement différents dans votre version du SSMS, mais devraient toujours être utiles pour l’explication des concepts de base.

1. Connectez-vous à une instance du moteur de base de données. Les événements étendus sont pris en charge à partir de SQL Server 2014 (12.x), dans la base de données Azure SQL et Azure SQL Managed Instance.

2. Dans l’Explorateur d’objets, sélectionner Gestion > Événements étendus. Dans la base de données Azure SQL, les sessions d’événements sont limitées à la base de données. L’option Événements étendus se trouve donc sous chaque base de données, et non sous Gestion. Cliquez avec le bouton droit sur le dossier Sessions et sélectionnez Nouvelle session. La boîte de dialogue Nouvelle Session est préférable à l’Assistant de Nouvelle Session, bien que les deux soient similaires.

3. Sélectionnez la page Général. Ensuite, tapez YourSession, ou tout autre nom de votre choix, dans la zone de texte Nom de session. Ne cliquez pas encore sur OK, car vous devez encore saisir des détails sur d’autres pages.

   

4. Sélectionnez la page Événements.

   

5. Dans la zone de Bibliothèque d’événements, dans la liste déroulante, choisissez Noms d’événements uniquement.

   • Tapez sql_statement_ dans la zone de texte. Cette option filtre la liste pour afficher uniquement les événements portant sql_statement_ dans le nom.
   • Faites défiler et sélectionnez l’événement nommé sql_statement_completed.
   • Cliquez sur le bouton flèche droite > pour déplacer l’événement vers la zone Événements sélectionnés.

6. Sur la page Événements, sélectionnez le bouton Configurer. La boîte Options de configuration des événements s’ouvre pour les événements sélectionnés.

   

7. Sélectionnez l’onglet Filtre (prédicat). Ensuite, cliquez sur Sélectionner ici pour ajouter une clause. Nous configurons ce filtre (également appelé prédicat) pour capturer toutes les instructions SELECT qui ont une clause HAVING.

8. Dans la liste déroulante Champ, choisissez sqlserver.sql_text.

   • Pour Opérateur, choisissez like_i_sql_unicode_string. Ici, i dans le nom de l’opérateur signifie insensible à la casse.
   • Pour Valeur, saisissez %SELECT%HAVING%. Ici, les signes de pourcentage sont des caractères génériques représentant n’importe quelle chaîne de caractères.

   Remarque

   Dans le nom en deux parties du champ, sqlserver est le nom du package et sql_text est le nom du champ. L’événement que nous avons choisi précédemment, sql_statement_completed, doit être dans le même package que le champ que nous choisissons.

9. Sélectionnez la page Stockage de données.

10. Dans la zone Cibles, sélectionner Cliquer ici pour ajouter une cible.

    • Dans la liste déroulante Type, choisissez event_file. Cela signifie que les données d’événement seront stockées dans un fichier que nous pouvons ouvrir et consulter ultérieurement. Dans la base de données Azure SQL et Azure SQL Managed Instance, les données d’événements sont stockées dans des blobs Azure Storage.

    Remarque

    À compter de SQL Server 2019 (15.x), vous pouvez utiliser Stockage Blob Azure dans une cible event_file dans SQL Server.

    

11. Dans la zone Propriétés, tapez un chemin complet et un nom de fichier dans la zone de texte Nom de fichier sur le serveur. Vous pouvez également utiliser le bouton Parcourir. L’extension de nom de fichier doit être xel. Dans notre exemple, nous avons utilisé C:\Temp\YourSession_Target.xel.

    

12. Sélectionnez la page Avancé. Réduisez la Latence maximale de répartition à 3 secondes.

13. Cliquez sur OK en bas pour créer cette session d’événements.

14. De retour dans l’Explorateur d’objets, ouvrez ou actualisez le dossier Sessions et consultez le nouveau Node pour YourSession. La session n’est pas encore démarrée. Vous la démarrez plus tard.

    

Modifier une session d’événement dans SSMS

Dans l’Explorateur d’objets de SSMS, vous pouvez modifier votre session d’événements en cliquant avec le bouton droit sur son Node, puis en sélectionnant Propriétés. La même boîte de dialogue constituée de plusieurs pages s’affiche.

Créer une session d’événements à l’aide de T-SQL

Dans l’IU des événements étendus SSMS, vous pouvez générer un script T-SQL pour créer votre session d’événements comme suit :

• Cliquez avec le bouton droit sur le Node de session d’événements, puis sélectionnez Session de script comme > CREATE dans > Presse-papiers.
• Collez-le dans un éditeur de texte.

Voici l’instruction T-SQL générée CREATE EVENT SESSION pour YourSession :

CREATE EVENT SESSION [YourSession]
    ON SERVER -- For SQL Server and Azure SQL Managed Instance
    -- ON DATABASE -- For Azure SQL Database
    ADD EVENT sqlserver.sql_statement_completed
    (
        ACTION(sqlserver.sql_text)
        WHERE
        ( [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%SELECT%HAVING%')
        )
    )
    ADD TARGET package0.event_file
    (SET
        filename = N'C:\Temp\YourSession_Target.xel',
        max_file_size = (2),
        max_rollover_files = (2)
    )
    WITH (
        MAX_MEMORY = 2048 KB,
        EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 3 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
    );
GO

DROP conditionnel de la session d’événements

Avant l’instruction CREATE EVENT SESSION, vous pouvez exécuter une instruction DROP EVENT SESSION de manière conditionnelle si une session portant le même nom existe déjà. Cela supprime la session existante. Sans cela, la tentative de création d’une session portant le même nom provoque une erreur.

IF EXISTS (SELECT *
      FROM sys.server_event_sessions
      WHERE name = 'YourSession')
BEGIN
    DROP EVENT SESSION YourSession
          ON SERVER;
END
GO

Démarrer et arrêter la session d’événements à l’aide de T-SQL

Quand vous créez une session d’événements, par défaut, elle ne démarre pas automatiquement. Vous pouvez démarrer ou arrêter votre session d’événements à tout moment à l’aide de l’instruction T-SQL ALTER EVENT SESSION suivante.

ALTER EVENT SESSION [YourSession]
      ON SERVER
    STATE = START; -- STOP;

Vous avez l’option de configurer la session d’événements pour qu’elle démarre automatiquement lorsque l’instance du moteur de base de données est démarrée. Voir le mot clé STARTUP STATE = ON dans CREATE EVENT SESSION.

L’IU de SSMS propose une case à cocher correspondante, Démarrer la session d’événements au démarrage du serveur, dans la page Général > Nouvelle session.

Tester une session d’événements

Testez votre session d’événement en suivant les étapes suivantes :

1. Dans l’Explorateur d’objets, cliquez avec le bouton droit sur le Node, puis cliquez sur Démarrer la session.
2. Lors de la connexion au même serveur (ou à la même base de données dans la base de données Azure SQL) où vous avez créé la session d’événements, exécutez l’instruction suivante SELECT...HAVING quelques fois. Envisagez de modifier la valeur de la clause HAVING pour chaque exécution, en basculant entre 2 et 3. Cela vous permet de voir les différences dans les résultats.
3. Cliquez avec le bouton droit sur le Node de votre session et sélectionnez Arrêter la session.
4. Lisez la sous-section suivante sur la manière de sélectionner et afficher les résultats.

SELECT c.name,
    COUNT(*) AS [Count-Per-Column-Repeated-Name]
FROM sys.syscolumns AS c
INNER JOIN sys.sysobjects AS o
    ON o.id = c.id
WHERE o.type = 'V'
    AND c.name LIKE '%event%'
GROUP BY c.name
HAVING Count(*) >= 3 --2     -- Try both values during session.
ORDER BY c.name;

Pour être exhaustif, voici l’exemple de sortie de la version précédente SELECT...HAVING.

/* Approximate output, 6 rows, all HAVING Count >= 3:
name                   Count-Per-Column-Repeated-Name
---------------------  ------------------------------
event_group_type       4
event_group_type_desc  4
event_session_address  5
event_session_id       5
is_trigger_event       4
trace_event_id         3
*/

Afficher les données de la session d’événement au format XML

Dans une fenêtre Requête dans SSMS, exécutez l’instruction suivante SELECT pour afficher les données d’événement capturées par votre session. Chaque ligne représente une occurrence d’événement. Le type de données de la colonne CAST(... AS xml) passe de nvarchar à XML. Cela vous permet de sélectionner la valeur de colonne pour l’ouvrir dans une nouvelle fenêtre pour faciliter la lecture.

Remarque

La cible event_file insère toujours une partie numérique dans le nom de fichier xel. Avant de pouvoir exécuter la requête suivante, vous devez copier le nom complet réel du fichier xel qui inclut cette partie numérique et le coller dans l’instruction SELECT. Dans l’exemple suivant, la partie numérique est _0_131085363367310000.

SELECT object_name,
    file_name,
    file_offset,
    event_data,
    'CLICK_NEXT_CELL_TO_BROWSE_XML RESULTS!' AS [CLICK_NEXT_CELL_TO_BROWSE_XML_RESULTS],
    CAST(event_data AS XML) AS [event_data_XML]
-- TODO: In the SSMS results grid, click this XML cell
FROM sys.fn_xe_file_target_read_file(
    'C:\Temp\YourSession_Target_0_131085363367310000.xel', NULL, NULL, NULL
);

Cette requête fournit deux façons d’afficher les résultats complets d’une ligne d’événement donnée :

• Exécutez SELECT dans SSMS, puis cliquez sur une cellule dans la colonne event_data_XML.

• Copiez la chaîne XML à partir d’une cellule dans la colonne event_data. Collez-la dans un éditeur de texte simple comme Bloc-notes Windows et enregistrez le fichier avec l’extension xml. Ouvrez ensuite le fichier dans un navigateur ou un éditeur capable d’afficher des données XML.

Données d’événement dans XML

Ensuite, nous observons qu’une partie des résultats est au format XML. Le XML suivant est édité pour des raisons de concision. <data name="row_count"> affiche une valeur de 6, qui correspond à nos 6 lignes de résultat précédemment affichées. Et nous pouvons voir l’ensemble de l’instruction SELECT.

<event name="sql_statement_completed" package="sqlserver" timestamp="2016-05-24T04:06:08.997Z">
  <data name="duration">
    <value>111021</value>
  </data>
  <data name="cpu_time">
    <value>109000</value>
  </data>
  <data name="physical_reads">
    <value>0</value>
  </data>
  <data name="last_row_count">
    <value>6</value>
  </data>
  <data name="offset">
    <value>0</value>
  </data>
  <data name="offset_end">
    <value>584</value>
  </data>
  <data name="statement">
    <value>SELECT c.name,
            COUNT(*) AS [Count-Per-Column-Repeated-Name]
        FROM sys.syscolumns AS c
        INNER JOIN sys.sysobjects AS o
            ON o.id = c.id
        WHERE o.type = 'V'
            AND c.name LIKE '%event%'
        GROUP BY c.name
        HAVING Count(*) >= 3 --2     -- Try both values during session.
    ORDER BY c.name;</value>
      </data>
</event>

Afficher les données de session d’événements dans SSMS

Il existe plusieurs fonctions avancées dans l’IU de SSMS qui vous permettent d’afficher les données capturées par une session d’événement. Pour plus d'informations, consultez Afficher les données d'événements dans SQL Server Management Studio.

Pour commencer, les options du menu local intitulées Afficher les données cibles et Surveiller les données actives sont proposées.

Afficher les données cibles

Dans l’Explorateur d’objets de SSMS, vous pouvez cliquer avec le bouton droit sur le Node cible qui se trouve sous le Node de votre session d’événements. Dans le menu local, sélectionner Afficher les données cibles. SSMS affiche les données.

L’affichage n’est pas mis à jour à mesure que de nouveaux événements se produisent dans une session. Mais vous pouvez sélectionner de nouveau Afficher les données cibles.

Surveiller les données actives

Dans l’Explorateur d’objets de SSMS, vous pouvez cliquer avec le bouton droit sur le nœud de votre session d’événements. Dans le menu local, sélectionner Surveiller les données actives. SSMS affiche les données entrantes au fur et à mesure qu’elles arrivent en temps réel.

Termes et concepts liés aux événements étendus

Le tableau suivant répertorie les termes utilisés pour les événements étendus et en donne la signification.

Terme	Description
event session	Construction centrée autour d’un ou plusieurs événements, associées à des éléments comme des actions et des cibles. L’instruction CREATE EVENT SESSION crée chaque session d’événements. Vous pouvez utiliser l’instruction ALTER sur une session d’événements pour la démarrer et l’arrêter. Une session d’événements est parfois simplement appelée session. Quand le contexte le précise, il s’agit d’une session d’événements. D’autres détails sur les sessions d’événements sont donnés dans : Sessions d’événements étendus.
event	Occurrence spécifique dans le système qui est surveillée par une session d’événements active. Par exemple, l’événement sql_statement_completed représente le moment auquel une instruction T-SQL donnée se termine. L’événement peut signaler sa durée et d’autres données.
target	Élément qui reçoit les données de sortie d’un événement capturé. La cible vous affiche les données. Les exemples incluent la cible event_file utilisée précédemment dans ce démarrage rapide et la cible ring_buffer qui conserve les événements les plus récents en mémoire. N’importe quel type de cible peut être utilisé pour n’importe quelle session d’événement. Pour plus d’informations, consultez Cibles des Événements étendus.
action	Champ connu de l’événement. Les données issues du champ sont envoyées à la cible. Le champ d’action est étroitement lié au filtre de prédicat.
predicate, ou filtre	Test de données d’un champ d’événement, utilisé pour que seul un sous-ensemble intéressant d’occurrences d’événements soit envoyé à la cible. Par exemple, un filtre peut inclure uniquement les occurrences d’événements sql_statement_completed où l’instruction T-SQL contient la chaîne HAVING.
package	Qualificateur de nom associé à chaque élément dans un ensemble d’éléments qui tournent autour d’un cœur d’événements. Par exemple, un package peut comporter des événements relatifs à du texte T-SQL. Un événement peut concerner toute l’instruction T-SQL dans un lot. Dans le même temps, un autre événement plus précis concerne des instructions T-SQL individuelles. De plus, pour toute instruction T-SQL, il existe des événements started et completed. Les champs appropriés aux événements sont également inclus dans le package avec les événements. La plupart des cibles sont dans package0 et sont utilisées avec les événements de nombreux autres packages.

Scénarios d’événements étendus et détails d’utilisation

Il existe de nombreux scénarios d’utilisation d’événements étendus pour surveiller et dépanner les problèmes du moteur de base de données et des charges de travail de requête. Les articles suivants fournissent des exemples utilisant des scénarios liés au verrouillage :

• Trouver les objets comportant le plus de verrous
  • Ce scénario utilise la cible histogramme, qui traite les données d’événement brutes avant de les afficher sous une forme résumée (compartimentée).
• Déterminer quelles requêtes détiennent des verrous
  • Ce scénario utilise la cible pair_matching, où la paire d’événements est sqlserver.lock_acquire et sqlserver.lock_release.

Comment découvrir les événements disponibles dans des packages

La requête suivante renvoie une ligne pour chaque événement disponible dont le nom contient la chaîne de trois caractères sql. Vous pouvez modifier la clause LIKE pour rechercher différents noms d’événements. Le jeu de résultats identifie également le package qui contient l’événement.

SELECT -- Find an event you want.
    p.name AS [Package-Name],
    o.object_type,
    o.name AS [Object-Name],
    o.description AS [Object-Descr],
    p.guid AS [Package-Guid]
FROM sys.dm_xe_packages AS p
INNER JOIN sys.dm_xe_objects AS o
    ON p.guid = o.package_guid
WHERE o.object_type = 'event' --'action'  --'target'
    AND p.name LIKE '%'
    AND o.name LIKE '%sql%'
ORDER BY p.name,
    o.object_type,
    o.name;

L’exemple de résultat suivant montre la ligne retournée, pivotée ici au format column name = value. Les données sont issues de l’événement sql_statement_completed qui était utilisé dans les exemples d’étapes précédents. La description de l’objet (un événement, dans cet exemple) sert de chaîne de documentation.

Package-Name = sqlserver
object_type  = event
Object-Name  = sql_statement_completed
Object-Descr = Occurs when a Transact-SQL statement has completed.
Package-Guid = 655FD93F-3364-40D5-B2BA-330F7FFB6491

Rechercher des événements à l’aide de l’IU de SSMS

Une autre option pour trouver des événements par leur nom est d’utiliser la boîte de dialogue Nouvelle session > Événements > Bibliothèque d’événements qui est illustrée dans la capture d’écran précédente. Vous pouvez taper un nom d’événement partiel et rechercher tous les noms d’événements correspondants.

Classes d’événements Trace SQL

Une description de l’utilisation d’événements étendus avec des classes d’événements et des colonnes Trace SQL est disponible ici : Afficher les événements étendus équivalents aux classes d’événements Trace SQL.

Event Tracing for Windows (ETW – suivi d'événements pour Windows)

Vous trouverez des descriptions de l’utilisation des événements étendus avec le suivi d’événements pour Windows (ETW) ici :

• Suivi d’événements pour cible Windows
• Superviser l’activité système à l’aide d’événements étendus

Sessions d’événements système

Dans SQL Server et Azure SQL Managed Instance, plusieurs sessions d’événements système sont créées par défaut et configurées pour démarrer au démarrage du moteur de base de données. Comme la plupart des sessions d’événements, elles consomment une petite quantité de ressources et n’affectent pas matériellement les performances de la charge de travail. Microsoft recommande que ces sessions restent activées et en cours d’exécution. Les sessions d’intégrité, en particulier la session system_health, sont souvent utiles pour la supervision et le dépannage des problèmes.

Vous pouvez voir ces sessions d’événements dans l’Explorateur d’objets de SSMS sous Gestion > Événements étendus > Sessions. Par exemple, dans SQL Server, ces sessions d’événements système sont les suivantes :

• AlwaysOn_health
• system_health
• telemetry_events

Fournisseur PowerShell

Vous pouvez gérer les événements étendus en utilisant le fournisseur SQL Server PowerShell. Pour plus d’informations, consultez Utiliser le fournisseur PowerShell pour les événements étendus.

Vues système

Les vues système pour les événements étendus sont les suivantes :

• Affichages catalogue : pour plus d’informations sur les sessions d’événements définies par CREATE EVENT SESSION.
• Vues de gestion dynamique (DMV) : pour des informations sur les sessions d’événements actives (démarrées).

Les instructions SELECT et JOIN des vues système pour les événements étendus dans SQL Server fournissent des informations sur :

• Comment joindre les vues
• Plusieurs requêtes utiles basées sur ces vues
• La corrélation entre :
  • Affichez des colonnes
  • ClausesCREATE EVENT SESSION
  • L’IU de SSMS

Annexe : requêtes pour rechercher les titulaires d’autorisations d’événement étendus

Les autorisations mentionnées dans cet article sont :

• ALTER ANY EVENT SESSION
• VIEW SERVER STATE
• CONTROL SERVER

L’instruction SELECT...UNION ALL suivante retourne des lignes indiquant les personnes disposant des autorisations nécessaires pour créer des sessions d’événements et interroger les affichages catalogue système sur les événements étendus.

-- Ascertain who has the permissions listed in the ON clause.
-- 'CONTROL SERVER' permission includes the permissions
-- 'ALTER ANY EVENT SESSION' and 'VIEW SERVER STATE'.
SELECT 'Owner-is-Principal' AS [Type-That-Owns-Permission],
    NULL AS [Role-Name],
    prin.name AS [Owner-Name],
    PERM.permission_name COLLATE Latin1_General_CI_AS_KS_WS AS [Permission-Name]
FROM sys.server_permissions AS PERM
INNER JOIN sys.server_principals AS prin
    ON prin.principal_id = PERM.grantee_principal_id
WHERE PERM.permission_name IN (
    'ALTER ANY EVENT SESSION',
    'VIEW SERVER STATE',
    'CONTROL SERVER'
)
UNION ALL
-- Plus check for members of the 'sysadmin' fixed server role,
-- because 'sysadmin' includes the 'CONTROL SERVER' permission.
SELECT 'Owner-is-Role',
    prin.name, -- [Role-Name]
    CAST((IsNull(pri2.name, N'No members')) AS NVARCHAR(128)),
    NULL
FROM sys.server_role_members AS rolm
RIGHT JOIN sys.server_principals AS prin
    ON prin.principal_id = rolm.role_principal_id
LEFT JOIN sys.server_principals AS pri2
    ON rolm.member_principal_id = pri2.principal_id
WHERE prin.name = 'sysadmin';

HAS_PERMS_BY_NAME, fonction

L’instruction suivante SELECT fait état de vos autorisations. Elle s’appuie sur la fonction intégrée HAS_PERMS_BY_NAME.

En outre, si vous avez l’autorité d’usurper temporairement l’identité d’autres connexions, vous pouvez supprimer les marques de commentaire de EXECUTE AS LOGIN et les instructions REVERT pour voir si d’autres connexions contiennent l’autorisation ALTER ANY EVENT SESSION.

--EXECUTE AS LOGIN = 'LoginNameHere';
SELECT HAS_PERMS_BY_NAME(NULL, NULL, 'ALTER ANY EVENT SESSION');
--REVERT;

Contenu connexe

• Aperçu des événements étendus
• Sessions d’événements étendus
• Cibles pour les événements étendus