Up 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

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

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

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

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

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

CREATE EVENT SESSION buildmysession ON SERVER
ADD EVENT sqlserver.sql_statement_completed ( 
ACTION ( sqlserver.sql_text )
WHERE (sqlserver.database_name = 'AdventureWorks2014'
--overloaded form of package0.equal_i_sql_unicode_string(database_name,'AdventureWorks2014')
--pred_compare or comparator predicate
AND package0.counter = 2 --pred_source or state data
) 
)
ADD TARGET package0.ring_buffer, --memory target
ADD TARGET package0.event_file (  SET filename = N'C:\Database\XE\pred_order6.xel' ) --file target
WITH ( MAX_DISPATCH_LATENCY = 1 SECONDS );

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