Note : Cet article fait partie de la série “Les techniques d’audit en base de données”. N’hésitez pas à consulter les autres articles de la série.
Dans un premier article, nous avons vu ce qu’était l’audit dans SQL Server 2008 et comment le mettre en place. Cette fonctionnalité est basé sur une autre nouveauté de la version 2008 : les extended events.
Attention : Cette fonctionnalité n’est disponible que dans la version Enterprise.
Ladies and gentleman, les extended events!
Vous le savez déjà, les événements sont quelque chose qui a lieu quelque part à un moment donné : Le concert du siècle, c’est le 23 Novembre au Yerba Buena Garden. Cette définition est exactement celle des events en développement : ce sont des « messages » qui indiquent que quelque chose se passe et qui est déclenché à un endroit précis du code.
Les Extended Events représentent une infrastructure d’événements performante, évolutive et configurable pour les serveurs. Ils sont donc intégrés à SQL Server 2008, mais fonctionnent au-delà de ce produit. Ce dernier point est très important : cela veut dire que le tracing SQL Server peut être regroupé dans le tracing Windows (ETW), côté des traces systèmes, IIS ainsi que les autres applications utilisant ce système.
Cette fonctionnalité est dans la lignée des améliorations effectuées depuis SQL Server 2000 pour le diagnostic et la résolution d’incidents, permettant ainsi de réduire le temps de ces opérations.
Les ingrédients de la recette
Les extended events utilisent de nombreux éléments. Voici une présentation de chacun d’eux.
Les packages
Les packages regroupent tout ce qui est nécessaire aux Extended Events. Ils sont contenus directement dans SQLSERVER.exe, et sont au nombre de quatre: package0, Sqlos, Sqlserver et SecAudit (utilisé uniquement par SQL server lui-même). Vous pouvez voir simplement leur contenu en exécutant la requête suivante.
select name, description, capabilities, capabilities_desc from sys.dm_xe_packages
Les événements
Ce sont les repères dans le code de SQL Server que l’on peut tracer. Bien qu’ils soient exécutés toujours de manière synchrone, cependant leur traitement lui peut être synchrone ou asynchrone. La liste des événements peut être récupérée simplement avec cette requête.
SELECT e.name, e.description FROM sys.dm_xe_objects e WHERE e.object_type = 'event'
Chacun de ces événements récupère un ensemble de colonnes, appelé payload, récupérable via ce code.
SELECT convert(varchar(55),o.name) as [Object Name]
,convert(varchar(25),c.name) as [Column Name]
,c.column_id as [Column ID]
,convert(varchar(12),c.column_type) as [Column Type]
FROM sys.dm_xe_objects o
JOIN sys.dm_xe_object_columns c ON o.name = c.object_name
WHERE
o.object_type = 'event' AND c.column_type != 'readonly'
ORDER BY [Object Name]
Les prédicats
Ils sont utilisés pour filtrer les événements lors de leur exécution. Vous pouvez utiliser tout d’abord des prédicats simples, puis des prédicats plus complexes. Il faudra toutefois veiller à leur complexité, qui peut influencer les performances du système.
SELECT * FROM sys.dm_xe_objects WHERE object_type in ('pred_compare', 'pred_source') ORDER BY name Les actions
Les actions sont des commandes qui apportent des informations additionnelles aux événements, tel que le contenu de la requête SQL. Elles peuvent être utilisées pour :
- · Agréger les données,
- · Ajouter des informations supplémentaires aux informations déjà existantes de l’événement levé,
- · Stocker des informations d’état dans un contexte local (via des variables de stockage),
- · Stopper l’exécution du serveur en créant un point de débogage,
- · Capturer les données et les analyser.
Les actions prédéfinies sont accessibles via la requête suivante.
SELECT * FROM sys.dm_xe_objects WHERE object_type in ('pred_compare', 'pred_source') ORDER BY name Les destinataires (targets)
Une fois que l’événement est levé, il est envoyé à un destinataire qui peut être aussi varié qu’un fichier, que l’event tracing Windows ou que le process courant. SQL Server 2008 comprend 6 types de destinataires :
- · Event bucketing : Conteneur basé sur les colonnes spécifiées lors de la création. La sortie est au format XML,
- · Event pairing : Permet de capturer les événements, de les associer et de ne retenir que ceux qui ne peuvent pas être associés. Par exemple, si on s’abonne aux événements lock_acquired et lock_released, seuls les événements lock_acquired pour lesquels le lock_released n’est pas encore déclenché seront enregistrés.
- · ETW target : écrit dans l’ETW,
- · Event file : écriture dans un fichier (un fichier de log et un fichier de métadonnées),
- · Synchronous event counter : comptabilise le nombre de fois que les événements sont levés,
- · Ring buffer : écrit les données en mémoire.
Les sessions
Tout comme pour les audits, les sessions regroupent tous les éléments nécessaires pour la capture et le traitement des événements. Vous pouvez créer de nombreuses sessions, chacune étant développée pour répondre à une question précise. Par défaut, les sessions créées ne sont pas actives. Vous devez les activer pour commencer la capture. Vous pouvez voir simplement la liste des sessions créées sur votre serveur.
SELECT * FROM sys.dm_xe_objects WHERE object_type in ('pred_compare', 'pred_source') ORDER BY name Si vous exécutez cette requête sur un serveur sur lequel vous avez des audits, ils seront présent dans les résultats.
Nous allons créer ici un exemple très simple d’extended events. Nous allons capturer chaque requête qui est exécutée et l’envoyer dans un compteur. Les commentaires dans le code source vous indiquent les différentes étapes.
-- 1. On supprime l'event si il existe déjà
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='SqlCounterEvent')
DROP EVENT SESSION [SqlCounterEvent] ON SERVER;
-- 2. On crée une nouvelle session...
CREATE EVENT SESSION [SqlCounterEvent]
ON SERVER
--...avec la capture de deux events
ADD EVENT sqlserver.sql_statement_starting,
ADD EVENT sqlserver.sql_statement_completed
-- .. et une destination
ADD TARGET package0.synchronous_event_counter
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
-- 3. On active la session
ALTER EVENT SESSION [SqlCounterEvent]
ON SERVER
STATE=START
-- 4. Exécutez ici des requêtes sur votre base de données
-- 5. Arrêtez la session
ALTER EVENT SESSION [SqlCounterEvent] ON SERVER
STATE = STOP
GO
-- 6. Récupération des résultats
SELECT name, target_name, CAST(target_data AS XML) target_data
FROM sys.dm_xe_sessions s
INNER JOIN sys.dm_xe_session_targets t
ON s.address = t.event_session_address
WHERE s.name = 'SqlCounterEvent' Les résultats sont récupérés sous forme de fichier XML. On peut ici voir, par event, le nombre de fois qu’il a été levé.
<CounterTarget truncated="0">
<Packages>
<Package name="package0" />
<Package name="sqlos" />
<Package name="sqlserver">
<Event name="sql_statement_starting" count="74" />
<Event name="sql_statement_completed" count="73" />
</Package>
<Package name="SecAudit" />
</Packages>
</CounterTarget>
Des outils pour vous aider
Il existe un certain nombre d’outils qui peuvent vous assister dans votre utilisation des extended events :
· PSDiag (http://support.microsoft.com/kb/830232) est un utilitaire de diagnostic utilisé principalement par le support technique Microsoft. Il vous permet de récupérer des fichiers journaux provenant de différentes applications, incluant SQL Server,
· Extended Events Manager (http://www.codeplex.com/ExtendedEventManager/) est une interface graphique vous permettant de créer, gérer et visualiser les données capturées par les événements,
· NTrace (http://www.codeplex.com/NTrace ) est une librairie permettant dans le code de vos applications le système de tracing ETW. Couplé aux Extended events, vous pouvez ainsi créer un tracing de bout en bout pour vos applicatifs,
· XPerf (http://blogs.technet.com/askperf/archive/2008/06/27/an-intro-to-xperf.aspx) est un outil vous permettant de gérer les traces ETW, et ainsi analyser plus facilement les données provenant de SQL Server, du système et de vos applicatifs.
Conclusion
Les extended events sont de loin l’outil de diagnostic et de résolution d’incidents le plus performant dans SQL Server. Leur maitrise demande cependant un certain exercice. Il est également important de conserver à l’esprit les autres outils de diagnostics existants.