Les Evénements Etendus avec SQL Server 2008

Une interface unique d'audit des instances et des bases de donnéespour SQL Server

Publié le 28 avril 2012 et mis à jour le 28 avril 2012

Par Nicolas Souquet

 

0. Introduction
I. Architecture logique des XE
I-0-1. I.1. Les packages
I-0-2. I.2. Les événements
I-0-3. I.3 Les cibles
I-0-4. I.4 Les actions
I-0-5. I.5 Les prédicats
I-0-6. I.6 Les « maps »
I-0-7. I.7 Les types
II. Gestion des sessions d'XE avec les vues et fonctions de gestion dynamique
II-0-1. II.1 Les DMVs de description de l'ensemble des caractéristiques possibles d'une session XE
II-0-2. II.2 Les DMVs de description des sessions XE en cours d'exécution
II-0-3. II.3 Les vues de catalogue de description des sessions XE, qu'elles soient démarrées ou arrêtées
II-0-4. II.4 La DMV du distributeur de XE
II-0-5. II.5 La DMF de dépouillement des fichiers binaires produits par une session XE
III. Quelques exemples guidés
III-0-1. III.1 Audit des échecs d'authentification : démarche de création d'une session XE pas à pas
III-0-2. III.2 Audit des requêtes grandes consommatrices de CPU et/ou d'E/S disque
III-0-3. III.3 Audit du nombre d'appels à des modules T-SQL par base de données utilisateur avec l'anneau de mémoire
IV. Le futur avec SQL Server 2012
V. Avantages et inconvénients de l'utilisation des XE
VI. Conclusion
Remerciements
Contact

0. Introduction

I. Architecture logique des XE

I-0-1. I.1. Les packages

• package0  contient les objets système qui sont nécessaires au bon fonctionnement d'une session d'événements étendus;

• s qlos contient tous les objets relatifs au fonctionnement du moteur de bases de données;
• sqlserver contient tous les objets relatifs à la vie d'une instance (attachement d'une base de données, exécution d'une requête, gestion des fonctionnalités comme FileStream et Service Broker, un split de page, .);

• SecAudit contient des objets dont SQL Server se sert pour les audits. Ce package est privé et on ne peut pas l'utiliser directement.

• Les évènements, c'est-à-dire ce que l'on cherche à capturer (tout comme lorsqu'on utilise SQL Profiler);

• Les cibles, qui consomment les événements, et permettent de définir le réceptacle de la capture (fichiers, anneau de mémoire, journaux d'événements, .);

• Les a ctions, qui sont des caractéristiques communes aux événements d'un package qui peuvent être capturées, ou qui permettent d'indiquer ce qui doit être fait lorsque les événements sont capturés;

• Les prédicats permettent de filtrer les événements, tout comme dans SQL Profiler (par exemple on ne cherchera que les requêtes dont le temps d'exécution est supérieur à 5 secondes, dans une base de données particulière), avant que l'événement soit consommé par la cible;

• Les «  maps  » et les t ypes qui sont des types plus ou moins complexes utilisés pour stocker les valeurs dans la structure binaire de capture de l'événement.

I-0-2. I.2. Les événements

• une requête commence ou se termine,
• l'acquisition, de la libération, ou du time-out d'acquisition d'un verrou,
• un split de page,
• l'exécution d'un point de contrôle (CHECKPOINT),
• la fin de la restauration d'une base de données,
• la libération de pages par le gestionnaire des tampons, .

I-0-3. I.3 Les cibles

• depuis un fichier binaire, spécifique aux événements étendus;
• directement dans la mémoire en anneau (« ring buffer ») de SQL Server;
• depuis le journal d'événements de Windows (Event Tracing for Windows)
• en comptant le nombre d'événements capturés par une une session, pour caractériser une charge de travail par exemple
• chercher une correspondance entre deux événements d'une même session.

• dans le journal d'événements et dans un fichier binaire
• mais aussi chercher les correspondances entre événements et les exposer dans un fichier binaire
• .

I-0-4. I.4 Les actions

I-0-5. I.5 Les prédicats

• Ne pas collecter les événements en-dessous ou en-dessus d'une valeur seuil ;
• Restreindre la capture à un certain nombre d'occurrences d'un événement (package0.counter = <uneValeur>)
• Ne capturer qu'un échantillon (package0.divides_by_uint64(package0.counter,4))
• limiter la consommation de ressources de certaines actions, comme un dump mémoire ou la capture de la pile d'appel
• .

• Les prédicats « source », c'est-à-dire ceux pour lesquels on peut utiliser la syntaxe de comparaison habituelle, comme on spécifierait un filtre dans la clause WHERE d'une requête, sans se soucier du type de la donnée du prédicat.

• Les prédicats de « comparaison » mettent en oeuvre une comparaison qui respecte le type de données de la colonne de l'objet filtré. Donc, si l'on souhaite capturer les événements d'une session seulement lorsque l'espérance de vie des pages de données en RAM passe en dessous de 5000, on écrira :

I-0-6. I.6 Les « maps »

I-0-7. I.7 Les types

II. Gestion des sessions d'XE avec les vues et fonctions de gestion dynamique

• Les DMVs de description de l'ensemble des caractéristiques possibles d'une session XE
• Les DMVs de description des sessions XE en cours d'exécution
• Les vues de catalogue de description des sessions XE, qu'elles soient démarrées ou arrêtées
• La DMV du distributeur de XE
• La DMF de dépouillement des fichiers binaires produits par une session XE

II-0-1. II.1 Les DMVs de description de l'ensemble des caractéristiques possibles d'une session XE

II-0-2. II.2 Les DMVs de description des sessions XE en cours d'exécution

II-0-3. II.3 Les vues de catalogue de description des sessions XE, qu'elles soient démarrées ou arrêtées

II-0-4. II.4 La DMV du distributeur de XE

II-0-5. II.5 La DMF de dépouillement des fichiers binaires produits par une session XE

• Le troisième paramètre est le premier fichier de la série que l'on souhaite lire. On peut le passer à NULL si l'on souhaite tous les lire.

• Le quatrième paramètre est l'offset à partir duquel on souhaite commencer la lecture.

III. Quelques exemples guidés

III-0-1. III.1 Audit des échecs d'authentification : démarche de création d'une session XE pas à pas

• sqlserver.sql_text  : le texte de la requête
• sqlserver.client_app_name  : le nom de l'application qui a tenté la connexion
• sqlserver.client_hostname  : le nom de la machine qui héberge l'application
• sqlserver.database_id  : l'identifiant de la base de données à laquelle l'application a tenté de se connecter. On la trouvera dans la vue système sys.databases
• sqlserver.session_nt_username  : le nom d'utilisateur qui a été utilisé lors de la tentative de connexion

III-0-2. III.2 Audit des requêtes grandes consommatrices de CPU et/ou d'E/S disque

• sp_statement_completed auditera les requêtes exécutées lors de l'appel d'un module T-SQL (procédure stockée, trigger, fonction) ;

• A l'inverse, sql_statement_completed auditera toutes les requêtes qui sont soumises au moteur de base de données en dehors d'un module T-SQL, comme par exemple lors de l'utilisation de sp_executesql, EXEC(@sql), ou par un job de l'Agent SQL Server;

• Toutes les requêtes
• Dont le nombre de lectures est supérieur ou égal à 100.000
• Et dont la durée dépasse 10 secondes
• Et qui s'exécutent dans la base de données dont le database_id égale 7 : (qui correspond à l'id de la base de d onnées ELSUKET sur mon instance SQL Server ici) .

-- Supprimer les fichiers relatifs à cette session

-- Création de la nouvelle session

CREATE EVENT SESSION long_running_queries ON SERVER
ADD EVENT sqlserver.sp_statement_completed
(
ACTION
(
sqlserver.sql_text
, sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.session_nt_username
)
WHERE (
reads >= 100000 -- en nombre de pages
OR duration >= 10000000 -- 10 secondes en microsecondes
)
AND sqlserver.database_id = 7 -- voir colonne sys.databases.database_id
)
, ADD EVENT sqlserver.sql_statement_completed
(
ACTION
(
sqlserver.sql_text
, sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.session_nt_username
)
WHERE (
reads >= 100000 -- en nombre de pages
OR duration >= 10000000 -- 10 secondes en microsecondes
)
AND sqlserver.database_id = 7 -- voir colonne sys.databases.database_id
)
ADD TARGET package0.asynchronous_file_target
(
SET FILENAME = N'C:\XE\long_running_queries.xel'
, METADATAFILE = N'C:\XE\long_running_queries.xem'
)
-- si le service est redémarré, la session démarre
-- avant que l'instance soit disponible aux connexions
WITH
( STARTUP_STATE = ON)
GO

ALTER EVENT SESSION long_running_queries ON SERVER
STATE = START;

Après avoir ré-exécuté la requête avec le CROSS JOIN, voyons ce que retourne la fonction  sys . fn_xe_file_target_read_file :

SELECT CAST(event_data AS xml) AS data
FROM sys.fn_xe_file_target_read_file
(
N'C:\XE\long_running_queries*.xel'
, N'C:\XE\long_running_queries*.xem'
, NULL
, NULL
)

< event name ="sql_statement_completed" package="sqlserver" id="60" version="1" timestamp="2012-04-14T11:58:57.224Z">
< data name ="source_database_id">
< value >7</value>
< text />
</ data >
< data name ="object_id">
< value >908208103</value>
< text />
</ data >
< data name ="object_type">
< value >20801</value>
< text />
</ data >
< data name ="cpu">
< value >5868</value>
< text />
</ data >
< data name ="duration">
< value >79657089</value>
< text />
</ data >
< data name ="reads">
< value >42736</value>
< text />
</ data >
< data name ="writes">
< value >7</value>
< text />
</ data >
< action name ="sql_text" package="sqlserver">
< value >SELECT * FROM master.dbo.spt_values AS A CROSS JOIN master.dbo.spt_values AS B</value>
< text />
</ action >
< action name ="client_app_name" package="sqlserver">
< value >Microsoft SQL Server Management Studio - Requête</value>
< text />
</ action >
< action name ="client_hostname" package="sqlserver">
< value >ELSUKET-SQL2008</value>
< text />
</ action >
< action name ="session_nt_username" package="sqlserver">
< value >ELSUKET-SQL2008\Nicolas SOUQUET</value>
< text />
</ action >
</ event >

On peut donc changer la requête de dépouillement des documents XML retournés par la fonction :

; WITH
CTE (event_data_xml) AS
(
SELECT CAST(event_data AS xml) AS data
FROM sys.fn_xe_file_target_read_file
(
N'C:\XE\long_running_queries*.xel'
, N'C:\XE\long_running_queries*.xem'
, NULL
, NULL
)
)
, DATA AS
(
SELECT DATEADD ( hour , DATEDIFF(hour, GETUTCDATE(), GETDATE()) , event_data_xml.value('(/event/@timestamp)[1]', 'datetime') ) AS time_stamp
, event_data_xml.value('(/event/data/value)[1]', 'int') AS database_id
, event_data_xml.value('(/event/action/value)[1]', 'nvarchar(max)') AS query
, event_data_xml.value('(/event/action/value)[2]', 'varchar(256)') AS appname
, event_data_xml.value('(/event/action/value)[3]', 'varchar(128)') AS hostname
, event_data_xml.value('(/event/action/value)[4]', 'varchar(256)') AS username
, event_data_xml.value('(/event/data/value)[4]', 'bigint') AS CPU
, event_data_xml.value('(/event/data/value)[5]', 'bigint') AS duration
, event_data_xml.value('(/event/data/value)[6]', 'bigint') AS reads
, event_data_xml.value('(/event/data/value)[7]', 'bigint') AS writes
FROM CTE
)
SELECT DB.name AS database_name
, D.time_stamp
, D.appname
, D.hostname
, D.username
, D.query
, D.CPU
, D.duration
, D.reads
, D.writes
FROM DATA AS D
INNER JOIN sys.databases AS DB ON D.database_id = DB.database_id
ORDER BY time_stamp

Ce qui donne :

Changeons maintenant cette requête en procédure stockée  :

CREATE PROCEDURE usp_spt_values_play_around
AS
BEGIN
SET NOCOUNT ON

SELECT *
FROM master.dbo.spt_values AS A
CROSS JOIN master.dbo.spt_values AS B
END
GO

EXEC dbo.usp_spt_values_play_around

En ré-exécutant la seconde requête de dépouillement, nous obtenons :

On voit donc qu'on a bien capturé deux événements : un pour l'exécution de la requête dans la procédure stockée, et un pour l'exécution de la procédure stockée. Malheureusement, nous obtenons deux fois le même texte, et assez curieusement, des consommations de ressources différentes.

III-0-3. III.3 Audit du nombre d'appels à des modules T-SQL par base de données utilisateur avec l'anneau de mémoire

J'ai créé cette session afin de chercher les bases de données qui ne sont jamais utilisées, sur une instance de SQL Server dédiée au développement, et qui hébergeait une centaine de base de données.

Comme le standard de l'entreprise est d'utiliser exclusivement des procédures stockées, mais qu'il existe aussi quelques triggers et fonctions, on peut donc chercher s'il existe un événement qui capture l'exécution d'un module T-SQL :

SELECT P.name AS package_name
, O.name AS event_name
, O.description
FROM sys.dm_xe_objects AS O
INNER JOIN sys.dm_xe_packages AS P
ON O.package_guid = P.guid
WHERE O.object_type = 'event'
AND O.name LIKE '%module%'

Ce qui retourne :

Voyons en détail s les caractéristiques de l'anneau de mémoire asynchrone (il en existe un synchrone, comme le montre le résultat de la requête pages 8 et 9 de la section I.3 ) :

SELECT O.name AS target_name
, OC.column_id
, OC.name AS column_name
, OC.column_type
, OC.column_value
, OC.description AS column_description
FROM sys.dm_xe_objects AS O
INNER JOIN sys.dm_xe_object_columns AS OC
ON O.name = OC.object_name
INNER JOIN sys.dm_xe_packages AS P
ON O.package_guid = P.guid
WHERE O.object_type = 'target'
AND O.name = 'asynchronous_bucketizer'

Dont le résultat est le suivant :

On peut donc définir la session comme suit :

CREATE EVENT SESSION database_usage ON SERVER
ADD EVENT sqlserver.module_start
(
ACTION (sqlserver.database_id)
WHERE (
sqlserver.database_id > 4 - La base de données n'est pas une base de données sytème
AND sqlserver.database_id <> 32767 - La base de données n'est pas Resource
)
)
ADD TARGET package0.asynchronous_bucketizer
(
SET filtering_event_name = 'sqlserver.module_start'
, source_type = 1 -- The source type: 0: filtering event name, 1: action name
, source = 'sqlserver.database_id'
, slots = 128 -- Number of buckets in the histogram
)
WITH
(
MAX_DISPATCH_LATENCY = 5 SECONDS)
GO

-- Starting the session

ALTER EVENT SESSION database_usage ON SERVER
STATE = START
GO

Sur un serveur en cours d'activité, il suffit d'attendre quelques secondes (un peu plus de 5 avec la session ci-dessus ) pour voir ce qui est capturé :

SELECT CAST(T.target_data AS xml) AS target_data
FROM sys.dm_xe_sessions AS S
INNER JOIN sys.dm_xe_session_targets AS T
ON S.address = T.event_session_address
WHERE S.name = 'database_usage'
AND T.target_name = 'asynchronous_bucketizer'

Etant donné qu'on peut avoir plusieurs sessions utilisant un anneau de mémoire, il est nécessaire de filtrer par le nom de la session. Le résultat de la requête est le suivant :

< BucketizerTarget truncated ="0" buckets="128">
< Slot count ="8" trunc="0">
< value >7</value>
</ Slot >
</ BucketizerTarget >

Ce que l'on transforme en données directement interprétables avec la requête suivante :

; WITH
CTE AS
(
SELECT CAST(T.target_data AS xml) AS target_data
FROM sys.dm_xe_sessions AS S
INNER JOIN sys.dm_xe_session_targets AS T
ON S.address = T.event_session_address
WHERE S.name = 'database_usage'
AND T.target_name = 'asynchronous_bucketizer'
)
, DATA AS
(
SELECT BZ.bucket.value('./value[1]', 'int') AS database_id
, BZ.bucket.value('./@count', 'int') AS calls_count
FROM CTE
CROSS APPLY target_data.nodes('BucketizerTarget/Slot') AS BZ(bucket)
)
SELECT DB.name AS database_name
, D.calls_count
FROM sys.databases AS DB
INNER JOIN DATA AS D
ON DB.database_id = D.database_id

Qui, dans mon cas, donne à l'exécution :

Voyons combien de mémoire consomme l'anneau :

SELECT name
, create_time
, total_buffer_size / 1024 AS total_buffer_size_KB
FROM sys.dm_xe_sessions
WHERE name = 'module_call'

Ce qui donne 4223 dans la colonne total_buffer_size_KB , soit 4MB . C'est négligeable sur un serveur.

IV. Le futur avec SQL Server 2012

Microsoft continue d'investir dans cet outil qui, nous l'avons vu, permet de collecter des données sur tout ce qui se passe dans une instance SQL Server.

S QL Server 2008 (R1) propose 255 événements ; SQL Server 2012 en propose 618 !

On notera les ajouts suivants :

• blocked_process_report
• sql_batch_starting / completed
• missing_column_statistics et missing_join_predicate , ou encore les événements do n t le nom commence par sp_cache _
• window_spool_ondisk_warning
• • la haute disponibilité (81, commençant par hadr _ ) et AlwaysOn (5, commençant par availability _ et alwayson _ )
  • les index ColumnStore (5, commençant par column_store _ )
  • La méthode de stockage FileTable (13, commençant par filetable _ )
  • Les événements concernant Unified Communications Stack (+22, contenant ou commençant par ucs_)
• les index de texte intégral (+ 10 , commençant soit par full_ , full_text _ , ou fulltext _ )
• le fichier du journal des transactions (+ 17, commençant par log_ )
• login et logout
• la possibilité de capturer les plan s de requête (+3, se terminant par _ showplan , attention à l'impact sur les performances !)
• les appels de procédures stockées distants (+56, commençant par query_rpc _ )
• les phases de récupération (+14, commençant par recovery _ et redo _ )
• plus de colonnes expos ées par de nombreux événements : c ertaines des colonnes que j'ai utilisé dans cet article ne sont plus disponibles ; par exemples reads a été remplacée par logical_reads , et physical_reads a été ajouté.

Pour ceux qui utilisent cette requête pour o btenir rétrospectivement la liste des deadlocks sous SQL Server 2008, elle produit un document XML qui, lorsqu'il est ouvert avec SQLProfiler , produit une erreur sous SQL Server 2008. Cela a été corrigé avec SQL Server 2012.

Enfin, Microsoft a ajouté une interface graphique :

En effectuant un clic droit sur le noeud sessions, on se voit proposer un assistant de création de session, ou bien une interface permettant de créer directement une session pour les utilisateurs un peu plus avancés.

Nul doute que cela facilitera également la création manuelle de sessions, puisque comme avec la plupart des interfaces graphique de SQL Server Management Studio, on a la possibilité de scripter ce que l'on vient de spécifier.

V. Avantages et inconvénients de l'utilisation des XE

Voici quelques éléments en faveur de l'utilisation des événements étendus :

• Ils fournissent une interface unique de diagnostic du moteur de base de données
• La spécification et le contrôle des sessions d'événements étendus se fait entièrement en T-SQL : il suffit donc de sauver le script pour le réutiliser sur d'autre serveurs, ou encore tester la session sur un serveur de développement, puis faire un déploiement de la session sur un groupe de serveurs Central Mangement Servers
• Les événements sont exécutés de manière synchrone, et peuvent ensuite être traités de la même façon, ou bien en asynchrone
• L'architecture interne des événements étendus repose à un niveau très bas dans l'architecture globale du moteur de base de données  confère aux XE un très faible impact sur les performances globales de la base de données, tout en leur permettant d'interagir avec n'importe quel composant du moteur de bases de données.
• Il est possible de faire démarrer une session XE avant que l'instance soit disponible aux utilisateurs, à l'aide de l'option STARTUP_STATE.

Le seul inconvénient que je vois sous SQL Server 2008 est qu'il ne propose pas d'interface graphique pour la création de sessions, ce qui aurait facilité l'appropriation de cette fonctionnalité majeure pour les DBAs.

On peut aussi considérer que l'écriture de requêtes SQL, SQLXML et XQuery n'est pas des plus simples, et que cela peut être un frein à l'utilisation des XE. Mais comme je l'ai déjà écrit en section II.5, avec un peu de pratique, l'écriture de telles requêtes devient (presque  ?) aisée.

VI. Conclusion

On l'a vu donc, Microsoft a su, avec la fonctionnalité XE, proposer une interface unique de diagnostic d'un grand nombre d'événements qui peuvent se produire aussi bien au niveau d'une instance de SQL Server qu'au niveau base de données.

Le XE remplacement allègrement le jeu d'outils proposé jusqu'avant l'introduction de SQL Server 2008, qu'il était nécessaire de combiner par un développement parfois complexe et peu souple.

Il ne fait nul doute que pour les DBAS, cette fonctionnalité est des plus utiles si ce n'est pas leur favorite. Si l'on peut voir l'absence d'interface graphique et le dépouillement d'un document XML en T-SQL comme un frein à son adoption, Microsoft a comblé le vide crée par le premier.

Pour ma part j'utilise les XE de façon régulière pour les besoins en base de données de mon employeur, et cela m'a permis de tracer des événements jusqu'alors inaccessibles, ou difficilement accessibles avec les outils proposés avec les précédentes versions . et de connaître la source de plusieurs problèmes de façon relativement simple.

J'espère que cet article vous a éclairé sous les XE, et qu'il vous aura donné envie de faire le saut pendant que votre entreprise envisage de passer à SQL Server 2012 (si ce n'est déjà fait), dans lequel vous serez probablement à l'aise pour auditer les nouvelles fonctionnalités à l'aide de XE.

Bon XE-Monitoring à tous !

Remerciements

Saisissez ici vos remerciements

Contact

Saisissez ici vos coordonnées pour pouvoir être contacté (MP forum, courriel, etc.).

               Version PDF   Version hors-ligne   Version eBooks

---