There is a wonderful amount of metadata available to be perused in Extended Events. Part of the trick is to know where and how to find it. I started to dive into the investigation of this metadata with the exploration of the Deployed Extended Event Session Settings discussed here. And that article is just one of many in the series that can be explored from here.
In this article, I am going to continue diving into the metadata of deployed Extended Event Sessions. While these articles are designed to build upon one another, the connection may not be entirely evident. Hopefully, that will be cleared up as I progress through the series. With that said, the topic at hand is to explore the metadata of the events deployed with an Extended Event Session.
Session – Event Metadata
Exploring the metadata for Events tied to a deployed session is actually rather easy. It is also strikingly similar to how one would explore the settings metadata for the deployed session. To explore this metadata, I will be bringing back a common event session script:
CREATE EVENT SESSION [demosession] ON SERVER ADD EVENT sqlserver.auto_stats ( SET collect_database_name = ( 1 )--Event Fields Screen ACTION ( package0.event_sequence--Actions Screen , sqlos.cpu_id , sqlserver.database_id , sqlserver.database_name ) WHERE ( [database_name] = N'AdventureWorks2014' )--Predicates Screen ) ADD TARGET package0.event_file ( SET filename = N'demosession'--Data Storage Screen , max_file_size = ( 50 )--Data Storage Screen , max_rollover_files = ( 6 ) --Data Storage Screen ), ADD TARGET package0.ring_buffer (SET max_events_limit=(666)--Data Storage Screen ,max_memory=(65536)--Data Storage Screen ,occurrence_number=(3)--Data Storage Screen ) WITH ( EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS--Advanced Screen , MAX_DISPATCH_LATENCY = 5 SECONDS--Advanced Screen , MAX_EVENT_SIZE = 2048 KB--Advanced Screen , MEMORY_PARTITION_MODE = PER_NODE--Advanced Screen , TRACK_CAUSALITY = ON--Opening Screen , STARTUP_STATE = ON--Opening Screen ); GO
And in similar fashion as the previous article, here is the script again with just the focal point of this article:
ADD EVENT sqlserver.auto_stats ( SET collect_database_name = ( 1 )--Event Fields Screen ... WHERE ( [database_name] = N'AdventureWorks2014' )--Predicates Screen )
Here, one can see that I have the event name, along with a customizable data point being set, and finally the predicate. These are all specific to the event metadata and are items to keep close at hand as I demonstrate how to explore the metadata for the events deployed to a session.
With that said, how does one explore the metadata tied to this event? One simply needs to query sys.server_event_session_events. That can be really simple in form, or it can be a bit more complex. First, I will explore the event metadata in a moderately basic fashion.
SELECT ses.name AS SessionName , sese.event_id , sese.name AS EventName , sese.package , sese.module , sese.predicate , sese.predicate_xml FROM sys.server_event_session_events sese INNER JOIN sys.server_event_sessions ses ON sese.event_session_id = ses.event_session_id WHERE ses.name = 'demosession';
Notice the connection in this script between sys.server_event_session_events and sys.server_event_sessions. I joined the two views in order to filter the results down to just the “demosession” session.
In this view, there will be one row for each event attached to the session. Each event in the session will also include the package name, module and predicate for that specific event. This helps to reinforce that the Extended Events engine allows a highly configurable filtration system for events occurring within the instance.
Recall from the discussions on predicates that a predicate is limited to 3000 characters? Looking at this view, it becomes apparent, at least in part, why that limit exists. The max length for the predicate column is NVARCHAR(3000) – or 3000 characters.
Also of note here is the predicate_xml column. A quick intro to the predicate xml format was given in the same article where the limit was introduced. Knowing that this is XML, let’s enhance the query a little bit. In addition to an enhancement for the XML, I will also add the exposure of the package metadata (remember – building blocks).
SELECT ses.name AS SessionName, sese.event_id, sese.name AS EventName, sese.package, sese.module, sese.predicate
,CAST(sese.predicate_xml AS XML) AS predicate_xml
,REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('',REVERSE(olm.name))-1)) AS DLLName
FROM sys.server_event_session_events sese
INNER JOIN sys.server_event_sessions ses
ON sese.event_session_id = ses.event_session_id
INNER JOIN sys.dm_xe_packages xp
ON xp.module_guid = sese.module
AND xp.name = sese.package
INNER JOIN sys.dm_os_loaded_modules olm
ON xp.module_address = olm.base_address
WHERE ses.name = 'demosession';This will return a well formatted (and clickable) XML column for the predicate_xml. In addition, I can see which dll exposes the package that contains the event in question. Being able to look at a well formatted predicate in XML is very handy when exploring this metadata and when trying to get a better understanding of how the event payload may or may not be trapped (remember the short-circuit topic?).
With a little imagination, one could evolve to a query such as this next one to look at the event information in a bit more detail.
SELECT ses.name AS SessionName , sese.event_id , sese.name AS EventName , sese.package , sese.predicate AS TSQLPredicate , CAST(sese.predicate_xml AS XML) AS predicate_xml , xoc.name AS PayloadColumn , xoc.column_type FROM sys.server_event_session_events sese INNER JOIN sys.server_event_sessions ses ON sese.event_session_id = ses.event_session_id INNER JOIN sys.dm_xe_object_columns xoc ON sese.name = xoc.object_name WHERE ses.name = 'demosession' AND xoc.capabilities_desc <> 'readonly';
With this last query, I have exposed the available payload for the event tied to the “demosession” session. In addition, I can also see which of these columns in the payload is “customizable” and which is of the “data” type. This is the sort of foundation that is useful when looking to build administrative type scripts for the toolbox. One such example of an useful script can be found in this article on shredding session XML.
Despite the seemingly simple nature of the catalog view, this is a pretty valuable view. This view exposes just enough data to help tie a few building blocks together as well as provide the means to create usable administrative scripts down the road. In this article, I exposed the predicate as well as a means to see the available payload attached to an event that has been deployed in a session. This last piece left one little piece yet to be discussed. That little piece involves the “customizable” type for the deployed session event payload. I will cover this in more detail in an upcoming article.
In case you have missed any articles in this series, here is the recap!
