Assembling a Session

With the core concepts in place as a good foundation to Extended Events, we start pushing these things together to help with assembling a session in Extended Events.

emptysession_puzzleUp to this point, I have taken a lot of time to discuss the various components of Extended Events. There is good reason for that. The components I have discussed are essential pieces of Extended Events and are critical to building useful sessions.

Previously, I only touched lightly on the topic of the concept of Event Sessions. In talking about the Sessions, I introduced means to see which sessions have been deployed, and which sessions are in a running state on the instance. But I have yet to explore the details of what a session is. It is now time to start diving into the details of what constructs a session.

Assembling a Session

event_puzzleThe first critical component of a session is the Event. I have covered the topic of what an Event is (here), as well as a deeper explanation into the payload of an Event (here). In short, the Event is an occurrence of something that interests you – the observer.

predicate_puzzleThe next component is the filtering mechanism known as predicates. I have covered this topic with the introductions into source predicates, comparison predicates, and predicate order.

Recall that after an event is collected, the next phase in the process is to evaluate the predicate(s). The predicate is a fairly significant component and should be duly considered when creating a session.

action_puzzleThe third component that I have previously introduced is that of the “Action”. An Action is an extra piece of data in the stream that is attached to the event payload.

The application of Action data to the session payload is the third step in the process. You can read more about Actions from here.

target_puzzleThe last critical component that I have discussed to this point is the Target.

A Target is the consumer of the event session data. It is crucial to direct the payload of the session to a consumer to be reviewed at a future time.

I introduced the need for the consumer when I introduced Targets – here.


3dmen_puzzleWhen we start to push these components together, one has the makings of an Event Session that can be used to trace and trap the events that are of interest to whatever issue may be important at the moment. Let’s see how this looks in the code.

In this session, I have determined that I want to trap the sql_text action on the second occurrence of a sql_statement_completed event in the AdventureWorks2014 database. When, this event occurs, I want the data to be consumed by both a memory target and by a file target. Notice that I have used multiple predicates and multiple targets.

Alternatively, let’s look at the script this way:


In this previous image, I have taken the code sample previously listed and color coded each session_puzzleof the critical components of an Event Session. Besides the color outlines, one could easily remark that the code looks pretty similar to other methods to create objects within SQL Server. When I create an Event Session, I use the standard DML syntax used when creating, altering, or dropping other objects like tables and procedures.

In my example, I have color coded each of the code segments to match the appropriate puzzle piece. For instance, the predicate is green. Notice that I used two types of predicates and even left a note about how I used the overloaded method for the pred_compare predicate type instead of the comparator object. Also of note here is that should I decide to send the session payload to multiple targets, that is well within reason as demonstrated here.

With the core concepts in place, building an Extended Event Session does become a fair bit easier.

To get a recap on this series, continue reading here.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.