Les Évènements Étendus avec SQL Server 2008

Un évènement est une action déclenchée par l'exécution de code (requêtes utilisateur ou système), par exemple :

• 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.

Pour ceux qui sont familiers de SQL Profiler, on retrouve les évènements fournis par ce dernier, avec en complément ceux fournis exclusivement avec les évènements étendus : le SQL Profiler de SQL Server 2008 expose 180 évènements, tandis que les XE en proposent pas moins de 254 sous SQL Server 2008, et 4 de plus sous SQL Server 2008 R2.

D'ailleurs, tout comme avec SQL Profiler, une session d'évènements étendus peut capturer plusieurs évènements.

On peut récupérer la liste des évènements de tous les packages à l'aide de la requête suivante :

SELECT P.name AS package_name
, P.description
, P.capabilities_desc
, O.name AS event_name
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'

Et voici un extrait du résultat :

Chaque évènement expose des attributs qui sont valués lorsque celui-ci est capturé (traduit dans la littérature par le terme payload), que l'on peut visualiser à l'aide de la requête suivante :

SELECT P.name AS package_name
, O.name AS event_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 = 'event'

Ce qui donne, pour l'évènement sp_statement_starting :

Il est très simple de voir que chaque évènement possède ses propres attributs : dans la requête suivante, la colonne event_attributes_count retourne le nombre d'attributs de chaque évènement :

SELECT P.name AS package_name
, O.name AS event_name
, COUNT(*) AS event_attributes_count
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 = 'event'
GROUP BY P.name, O.name
ORDER BY P.name, O.name

Les cibles consomment un évènement et permettent d'indiquer quel objet sera leur réceptacle. Les évènements capturés peuvent ainsi être extraits de plusieurs manières :

• 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 session, pour caractériser une charge de travail par exemple ;
• chercher une correspondance entre deux évènements d'une même session.

De la même manière que les évènements, on peut utiliser plusieurs cibles dans une même session.

On peut donc exposer les évènements capturés à la fois :

• 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 ;
• …

On peut connaître la liste de toutes les cibles et de leurs caractéristiques par la requête suivante :

SELECT P.name AS package_name
, O.name AS target_name
, OC.column_id
, OC.name AS column_name
, OC.column_type
, OC.capabilities_desc
, 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'

Voici donc les caractéristiques de la cible asynchronous_file_target :

On notera que la colonne capabilities_desc précise si l'attribut de la cible doit être obligatoirement spécifié (valeur mandatory) lors de l'utilisation de la cible dans la spécification d'une session.

De la même façon que pour les évènements, on peut récupérer la liste de toutes les cibles possibles :

SELECT P.name AS package_name
, O.name AS target_name
, O.capabilities_desc AS target_capabilities
, O.description AS target_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 = 'target'
ORDER BY package_name, target_name

Et l'on s'aperçoit ici que seuls deux packages exposent des cibles : package0 et SecAudit. Étant donné que l'on ne peut pas utiliser le package SecAudit puisqu'il est privé, on peut utiliser toutes les cibles du package0.

Les actions définissent les caractéristiques d'un évènement. Ces actions peuvent être soit communes à tous les évènements d'un package, soit spécifiques à un évènement particulier. On trouve la liste de ces actions ainsi qu'une brève description avec la requête suivante :

SELECT P.name AS package_name
, O.name AS action_name
, O.capabilities_desc AS action_capabilities
, O.description AS action_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 = 'action'
ORDER BY package_name, action_name

Dont le résultat pour le package SQL Server est :

Le résultat de cette requête montre que l'on peut collecter le CPU sur lequel s'est exécutée la requête, le nom de la machine cliente et de l'application qui a exécuté la requête, la base de données sur laquelle s'est exécutée la requête, le texte de la requête.

Ce sont tout autant de caractéristiques dont on peut se servir pour restreindre ce que l'on souhaite auditer, comme par exemple capturer les requêtes exécutées sur une base de données particulière (sqlserver.database_id), ou par un nom d'utilisateur donné (sqlserver.session_nt_user_name).

On remarquera que le package SecAudit n'apparaît pas dans la liste des packages retournés par cette requête. Ce n'est pas une surprise, puisque ce package est privé et supporte la fonctionnalité d'Audit introduite avec SQL Server 2008.

Les prédicats ont vocation à filtrer de manière plus fine qu'avec SQL Profiler une session d'évènements. Par exemple il est possible de :

• ne pas collecter les évènements en dessous ou au-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 ;
• …

Il existe deux types de prédicats :

• 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.

C'est ce que l'on a vu précédemment lorsqu'on souhaite filtrer sur une session pour une base de données particulière ou un utilisateur particulier (WHERE sqlserver.database_id = 12) ;

• les prédicats de « comparaison »mettent en œuvre 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 :

ADD EVENT sqlserver.buffer_manager_page_life_expectancy
WHERE (package0.less_than_equal_uint64(package0.count, 5000))

On trouve la liste des prédicats similairement aux évènements :

SELECT P.name AS package_name
, P.capabilities_desc AS package_capabilities
, O.name AS pred_source_name
, O.object_type
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 = 'pred_source'
OR O.object_type = 'pred_compare'

Dont un extrait du résultat est :

Les « maps » sont en fait des tables de correspondance pour les valeurs internes au moteur de base de données, de sorte qu'elles soient intelligibles. Elles sont similaires à une entité d'un modèle de données de base de données relationnelle SQL qui représente les divers états possibles d'une autre entité. Dans le cas présent, elles font correspondre, par exemple, le type d'attente 3 à LCK_M_S (attente d'acquisition de verrou de modification de schéma).

Voyons d'abord comment trouver les « maps » : c'est assez identique à ce que nous avons déjà vu :

SELECT P.name
, O.name AS event_name
, O.object_type
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 = 'map'

Voici le résultat abrégé :

Une autre vue de gestion dynamique expose les valeurs et leur sens : sys.dm_xe_map_values :

SELECT P.name AS package_name
, O.name AS map_name
, MV.map_key
, MV.map_value
FROM sys.dm_xe_objects AS O
INNER JOIN sys.dm_xe_packages AS P
ON O.package_guid = P.guid
INNER JOIN sys.dm_xe_map_values AS MV
ON MV.object_package_guid = O.package_guid
AND MV.name = O.name
WHERE O.object_type = 'map'

Voici un court extrait du résultat :

Les types représentent tous les types de données utilisés par l'ensemble des évènements qu'il est possible de capturer avec les XE. On notera que tout champ exposé par un évènement est typé. Les types prennent tout leur sens lors de la spécification des prédicats.

On en obtient la liste avec la requête suivante :

SELECT P.name AS package_name
, O.name AS type_name
, O.type_size
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 = 'type'

Dont une partie du résultat est la suivante :

Il existe cinq DMV qui permettent de connaître les caractéristiques (évènements, actions et cibles) de toutes les sessions XE en cours d'audit. Leur nom commence par sys.dm_xe_sessions_.

De la même façon, je vous renvoie vers la documentation sur Internet :

Voici une requête qui retourne toutes les caractéristiques de la session nommée system_health :

SELECT DISTINCT S.name AS session_name
, SEAM.event_name
, A.action_list
FROM sys.dm_xe_sessions AS S
INNER JOIN sys.dm_xe_session_event_actions AS SEAM
ON S.address = SEAM.event_session_address
CROSS APPLY(
SELECT PA.name + '.' + SEA.action_name + ', '
FROM sys.dm_xe_session_event_actions AS SEA
INNER JOIN sys.dm_xe_packages AS PA
ON SEA.action_package_guid = PA.guid
WHERE S.address = SEA.event_session_address
AND SEAM.event_name = SEA.event_name
FOR XML PATH ('')
) AS A(action_list)
WHERE S.name = 'system_health'
ORDER BY SEAM.event_name

En fait, la session nommée system_health est une session système activée par défaut sur SQL Server 2008 et qui collecte les évènements dans un anneau de mémoire (ring buffer en Anglais), en capturant par exemple les caractéristiques des 256 derniers deadlocks, ce qui permet de les retrouver de façon rétrospective.

Voyons comment trouver ses cibles :

SELECT S.name AS session_name
, PT.name + '.' + ST.target_name AS target_name
, CAST(ST.target_data AS xml) AS target_data
FROM sys.dm_xe_session_targets AS ST
INNER JOIN sys.dm_xe_sessions AS S
ON ST.event_session_address = S.address
INNER JOIN sys.dm_xe_packages AS PT
ON ST.target_package_guid = PT.guid

La colonne target_data de la DMV sys.dm_xe_session_targets retourne un document XML dans tous les cas, mais avec un contenu différent en fonction de la cible utilisée par la session concernée.

Il existe là aussi 5 vues qui permettent de gérer les sessions d'évènements étendus, qu'elles soient arrêtées ou démarrées (nous verrons plus tard qu'on l'on peut créer une session sans la démarrer, la démarrer par la suite, l'arrêter et la démarrer de nouveau).

Leurs noms commencent par sys.server_event_session. Je vous renvoie de nouveau vers la documentation disponible sur Internet :

La requête suivante retourne la liste de toutes les sessions d'évènements étendus enregistrées dans l'instance SQL Server :

SELECT name AS session_name
, event_retention_mode_desc
, max_dispatch_latency
, max_event_size
, max_memory
, memory_partition_mode_desc
, CASE startup_state
WHEN 1 THEN 'Démarre automatiquement avec l''instance'
ELSE 'Démarrée par action de l''utilisateur'
END AS startup_state
, track_causality
FROM sys.server_event_sessions

Voici comment trouver la liste des évènements et leurs actions :

SELECT S.name AS session_name
, SE.package + '.' + SE.name AS event_name
, REPLACE(REPLACE(SE.predicate, '[', ''), ']', '') AS event_predicate
, A.action_list
FROM sys.server_event_sessions AS S
INNER JOIN sys.server_event_session_events AS SE
ON S.event_session_id = SE.event_session_id
CROSS APPLY(
SELECT package + '.' + name + ', '
FROM sys.server_event_session_actions AS SEA
WHERE SEA.event_session_id = S.event_session_id
AND SEA.event_id = SE.event_id
FOR XML PATH ('')
) AS A(action_list)

C'est assez similaire avec les DMV pour les sessions en cours d'exécution ; néanmoins les jointures ne se font pas sur une adresse, mais sur un identifiant.

Le détail des options de session s'obtient comme suit :

SELECT S.name AS session_name
, SF.name AS session_option_name
, SF.value AS option_value
FROM sys.server_event_session_fields AS SF
INNER JOIN sys.server_event_sessions AS S
ON SF.event_session_id = S.event_session_id

Et voici pour les cibles :

SELECT S.name AS session_name
, ST.package + '.' + ST.name AS target_name
FROM sys.server_event_session_targets AS ST
INNER JOIN sys.server_event_sessions AS S
ON ST.event_session_id = S.event_session_id

La DMV sys.dm_os_dispatcher_pools retourne les caractéristiques du distributeur d'évènements étendus pour les sessions en cours d'exécution.

En effet, les XE ont été conçus pour avoir un impact minimal sur les performances de l'instance auditée. Donc, lorsqu'un même évènement est audité par plusieurs sessions, il n'est en fait capturé qu'une seule fois et distribué à toutes ces sessions, suivant que les filtres de celles-ci vérifient les caractéristiques de l'évènement capturé. C'est un comportement différent de SQL Profiler, où les évènements sont capturés, autant de fois qu'ils se produisent, par toutes les traces en cours d'exécution.

Il n'y a donc qu'un distributeur d'évènements par instance de SQL Server, donc pour en connaître le statut, la requête est très simple :

SELECT *
FROM sys.dm_os_dispatcher_pools

La fonction sys.fn_xe_file_target_read_file est le seul outil à disposition qui permet de retourner les caractéristiques des évènements qui ont été capturés par une session XE dont la cible est un fichier binaire. Elle s'utilise comme suit :

SELECT CAST(event_data AS xml) AS data
FROM sys.fn_xe_file_target_read_file
(
'D:\dossier\uneSessionXE*.xel' -- fichier de capture des évènements XE
, 'D:\dossier\uneSessionXE*.xem' -- fichier de métadonnées de la session
, NULL
, NULL
)

On notera que cette fonction retourne une liste de documents XML sous la forme d'une chaîne de caractères, ce qui facilite l'extraction des données via les requêtes de type XML de SQL Server, comme nous le verrons dans les exemples.

Le premier paramètre est le chemin du fichier dans lequel on a capturé tous les évènements de la session. On peut utiliser les jokers si l'on ne souhaite pas spécifier le nom du fichier à lire, sachant qu'il peut y en avoir plusieurs par session (un à chaque démarrage de la session).

Le second paramètre est le chemin du fichier de métadonnées qui décrit les caractéristiques de la session. De la même façon, il peut y en avoir plusieurs par session.

On a plusieurs fichiers par session si l'on arrête puis redémarre une session, ou si l'on supprime une session et qu'on la recrée avec le même nom.

On notera que ces deux fichiers sont indissociables : c'est-à-dire que si l'on transfère seulement le fichier de capture des évènements à une autre personne sans le fichier de métadonnées, cette personne sera dans l'impossibilité de le lire.

Les deux précédents paramètres sont obligatoires, mais les deux suivants ne le sont pas. En revanche si l'on spécifie l'un d'entre eux, il faut que l'autre soit également valué :

• 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.

On peut trouver la liste des fichiers et de leurs offsets à l'aide de la requête suivante :

SELECT DISTINCT file_name
, file_offset
FROM sys.fn_xe_file_target_read_file
(
'D:\dossier\uneSessionXE*.xel' -- fichier de capture des évènements XE
, 'D:\dossier\uneSessionXE*.xem' -- fichier de métadonnées de la session
, NULL
, NULL
)

On cherche ici à collecter sur le long terme les requêtes contre-performantes. Ce sont typiquement les requêtes qui sont longues à s'exécuter, consomment beaucoup de CPU, et/ou les requêtes qui lisent beaucoup de pages. Ce n'est pas exhaustif, mais cela couvre assez largement les requêtes qui méritent d'être revues et optimisées.

Bien sûr on peut auditer la même chose avec SQL Profiler, mais il est clair que les Évènements Étendus ont un impact bien plus léger une instance SQL Server que SQL Profiler, surtout lorsque la charge de travail est de type OLTP (de très nombreuses requêtes, majoritairement simples).

Avec la même démarche que dans la section précédente, voyons s'il existe des évènements permettant de capturer les requêtes exécutées, que ce soit par des procédures stockées, ou avec sp_executesql, ou encore par un job.

SELECT name, description
FROM sys.dm_xe_objects
WHERE object_type = 'event'
AND name LIKE '%statement%'

Ce qui retourne:

Voyons maintenant quelles sont les données capturées par ces évènements :

SELECT O.name AS event_name
, C.name AS column_name
, C.type_name AS column_data_type
, C.column_type AS column_usage
FROM sys.dm_xe_object_columns AS C
INNER JOIN sys.dm_xe_objects AS O
ON C.object_name = O.name
WHERE O.name LIKE '%statement%'
AND C.column_type <> 'readonly'

On voit clairement que les évènements sql_statement_completed et sp_statement_completed collectent les caractéristiques des requêtes dès que celles-ci ont terminé leur exécution :

• 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) ;
• à 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, par exemple lors de l'utilisation de sp_executesql, EXEC(@sql), ou par un job de l'Agent SQL Server.

Ces deux évènements nous permettent d'appliquer des filtres sur les caractéristiques des requêtes que l'on souhaite capturer : nombre de lecture s et d' écritures de pages , temps CPU consommé, durée de la requête, contexte de base de données .

Malheureusement le niveau d'imbrication (ex. : une procédure stockée qui en appelle une autre, qui qui appelle à son tour un trigger, qui lui-même appelle une procédure stockée qui exécute du code T-SQL dynamique à l'aide de sp_executesql ) n'est pas exposé par les deux évènements ci-dessus , mais il l'est par les évènements sql_statement_starting et sp_statement_star ted .

Avec ces deux évènements, on peut créer la session suivante :

CREATE EVENT SESSION long_running_queries ON SERVER
ADD EVENT sqlserver.sp_statement_completed
(
ACTION (sqlserver.sql_text)
WHERE(
reads >= 100000 -- en nombre de pages
OR duration >= 10000000 -- 10 secondes en microsecondes
)
AND sqlserver.database_id = 7 -- voircolonnesys.databases.database_id
)
, ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text)
WHERE(
reads >= 100000 -- en nombre de pages
OR duration >= 10000000 -- 10 secondes en microsecondes
)
AND sqlserver.database_id = 7 -- voircolonnesys.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;

Ici on souhaite auditer :

• 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 données ELSUKET sur mon instance SQL Server ici).

Voyons donc ce que l'on capture à l'exécution de la requête suivante :

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

La machine virtuelle sur laquelle j'ai réalisé l'article est paramétrée avec 1 GB de mémoire vive. La table dbo. spt_values contenant 2506 lignes, la requête retournera donc 6.280. 036 lignes , ce qui devrait prendre relativement longtemps à calculer compte tenu des caractéristiques physiques de la machine virtuelle.

La requête qui permet de dépouiller ce qui est collecté par cette session est la suivante :

;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/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.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

Durant l'exécution de la requête avec CROSS JOIN, bien que le temps total d'exécution soit supérieur à 2 minutes, l'exécution de la requête ci-dessus ne retourne rien : cela correspond tout à fait au nom des évènements que nous avons utilisés pour définir la session : ils contiennent tout le mot completed .

En revanche, dès la fin de la session, on obtient :

Le nombre de lectures (colonne reads ) n'a pas excédé le seuil de capture que nous avions fixé (100 000), mais il a amplement dépassé celui de la durée : la requête a duré 123 721 646 µs , et le seuil est à 10 000 000 µs. Nous avons toutes les autres données que nous voulons pour cet audit, en particulier la base de données dans laquelle s'est exécutée la requête, et à quelle heure.

Il est nécessaire d'utiliser DATEDIFF(hour, GETUTCDATE(), GETDATE()), puisque l'heure d'un évènement est toujours enregistrée en UTC.

Mais il serait bien utile aussi de savoir quelle application, sur quelle machine, et par quel utilisateur cette requête a été exécutée. Il suffit donc de modifier la session en ajoutant les actions sqlserver.client_app_name , sqlserver.client_hostname et sqlserver.session_nt_username , que l'on trouve avec la requête qui liste toutes les actions à la section I.4 :

-- Créons une toute nouvelle session

DROP EVENT SESSION long_running_queries ON SERVERGO

-- 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 -- voircolonnesys.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 -- voircolonnesys.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
)

<eventname=« sql_statement_completed »package=« sqlserver »id=« 60 »version=« 1 »timestamp=« 2012-04-14T11:58:57.224Z »>
<dataname=« source_database_id »>
<value>7</value>
<text />
</data>
<dataname=« object_id »>
<value>908208103</value>
<text />
</data>
<dataname=« object_type »>
<value>20801</value>
<text />
</data>
<dataname=« cpu »>
<value>5868</value>
<text />
</data>
<dataname=« duration »>
<value>79657089</value>
<text />
</data>
<dataname=« reads »>
<value>42736</value>
<text />
</data>
<dataname=« writes »>
<value>7</value>
<text />
</data>
<actionname=« sql_text »package=« sqlserver »>
<value>SELECT * FROM master.dbo.spt_values AS A CROSS JOIN master.dbo.spt_values AS B</value>
<text />
</action>
<actionname=« client_app_name »package=« sqlserver »>
<value>Microsoft SQL Server Management Studio - Requête</value>
<text />
</action>
<actionname=« client_hostname »package=« sqlserver »>
<value>ELSUKET-SQL2008</value>
<text />
</action>
<actionname=« 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)') ASusername
, 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.

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 systè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'

Étant 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 :

<BucketizerTargettruncated=« 0 »buckets=« 128 »>
<Slotcount=« 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.