Having covered the core concepts of deployed session metadata (events, actions, targets, predicates), there is one more topic to cover. This is more of a subset of those concepts in that this applies to more than one of the core concepts, but it isn’t really a stand-alone topic. This is the sub-topic of SET operations. This also happens to be the topic I was referring to when I covered the metadata of session events and targets.
SET operations in extended events are the extra configurations that can be made to various components. As an example, a target can be configured with certain properties via the use of a SET operation when creating the Extended Event Session. The best way to see this is by seeing it in action.
SET Operations and Metadata
It is time, once again, to break out that tried and true example script that I have been using throughout the past several articles.
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
If I follow the same pattern as has been done in previous articles, I could eliminate everything from the script that is not a set operation. Instead, I will just highlight those SET operations with the following image.
Keeping with the same color-coding that has been used in the previous articles, I have added just a few highlights. Highlighted in purple, are all of the SET operations for this particular session creation script. In the case of the Targets, these set operations are properties of the Target. In the case of the Event SET operation, this happens to be a “customizable” type within the Event payload.
Now that we know what these SET operations can be, and where they might exist, how does one access this information? It was seen in the article about the deployed Event metadata and also the article about deployed Target metadata, that this data is not visible within those views. This is where the entity attribute value data model comes back into play within Extended Events. To expose this metadata, one needs to query the sys.server_event_session_fields system catalog view.
Querying the sys.server_event_session_fields view directly will produce some interesting information. While interesting, it is less than complete. Sure, if you query the view, you can see various attributes and the value of those attributes. Unfortunately, the picture is just not complete.
For instance, if you query this view, you will see that there is a name, value, session id, and even an object id. The object id is not entirely descriptive as to what exactly it is (and neither is the documentation). However, one could make the quick presumption based on some of the attribute names and arrive at a conclusion that the object id maps to the id field of the other metadata views (e.g. event_id and target_id). Working with this information, a more clear picture can be painted via the following query.
SELECT ses.name AS SessionName , sesf.name AS FieldName , sesf.value AS FieldValue , CASEWHEN ISNULL(sese.event_session_id, '') <> '' THEN 'Event' WHEN ISNULL(sest.event_session_id, '') <> '' THEN 'Target' END AS ObjectType , sese.name AS EventName , sest.name AS TargetType FROM sys.server_event_session_fields sesf INNER JOIN sys.server_event_sessions ses ON ses.event_session_id = sesf.event_session_id LEFT OUTER JOIN sys.server_event_session_events sese ON sesf.object_id = sese.event_id AND ses.event_session_id = sese.event_session_id LEFT OUTER JOIN sys.server_event_session_targets sest ON sesf.object_id = sest.target_id AND ses.event_session_id = sest.event_session_id WHERE ses.name = 'demosession';
This query joins back to the event metadata and target metadata based on the object_id. If there is a SET operation for the Event or the Target, then this query will return those results. You may be wondering why I am only looking at the sys.server_event_session_target and _event views. As it happens, the core component of Actions does not have a set operation. As for the Predicate core component, recall that the Predicate is stored as a property of the Event.
Looking at the results of that query for the “demosession” session, I would see this:
Comparing these results to the script, I can easily see how the properties start to map and see that the presumptions that I made were accurate. Now I can see all of the critical information that is affiliated to the creation of a Target for an Event Session.
An important note about one value, in particular, needs to be made. The filename property only has the name of the file in this particular case. If I had specified more than just a filename, and decided to include a specific filepath for instance, then the path and filename would be the value for this property. But since I only provided a name for the file without a path at session creation, the file was created in the default path and the engine will look in that default path automatically if a path is not created (when looking to query the file for example).
To this point, I have now covered every facet of how to retrieve the metadata for a deployed Session. I have demonstrated how to retrieve everything from the Target to the Event to the Predicate and all the way down to the SET operations and specific Session level settings. With all of this information, I will show what can be done with it in the next article.
With all that has been covered (and the length of this series) on the topic of Extended Events, it is easy to have missed an article in the series, or to even need a quick recap of one of the topics. If this is the case, then feel free to peruse the table of contents – here.
