Among the several core concepts of Extended Events is the concept of storing event payloads / data. When an event fires, it would not be far-fetched to want to be able to review that data at some time after the fact. How often are you sitting there watching the system just waiting for an error or event to occur? Hopefully, you aren’t just staring at the monitor waiting for it to happen.

A thought comes to mind for this kind of monitoring – “a watched kettle never boils.” And while that is not entirely true, the intent is close. It seems that while you are watching for an event to occur, it just doesn’t happen. Or at least, it waits to happen at the precise moment you blink and consequently you miss it.

Being able to store the session data is essential to a good monitoring solution. Within Extended Events, this storage comes via the concept of targets. Recall (from the Objects Article) that a target is the destination of the payload and thus is a consumer of the event payloads.

Targets

Microsoft has provided several consumers for us to use when creating Extended Event Sessions. In addition to the targets readily available to use when creating a session, there are a few that are “hidden” targets and are reserved for internal use. Private or public, we will take a quick peek at targets as a whole, but we will spend most of our time looking at what is available to us as a consumer.

So what kind of event consumers / targets can one implement to direct output from a Session? Let’s see:

SELECT DISTINCT xo.name AS TargetName
,xo.description AS TargetDescription
,xo.capabilities_desc 
FROM sys.dm_xe_objects xo
WHERE xo.object_type = 'target'
AND (xo.capabilities & 1 = 0
OR xo.capabilities IS NULL);

This will produce the following results (well, if you are using SQL Server 2008 or R2, your results will vary):

This provides an array of options with some nice opportunities to do different things with EXtended Events. One can either direct the event payload to memory, to a file, to a counter object, or try to match the event with another event. These are some good options. However, if you are using SQL Server 2008, your results would have been different. Let’s compare and contrast real quick.

While, the same general targets are available, they are implemented differently across the versions of SQL Server. This is important to remember when trying to recreate an Extended Event Session on SQL Server 2008R2 that was designed on SQL Server 2012, and was set to output to an event_file target. Trying to recreate that session can result in a loss of hair due to the difference in target names between versions.

I mentioned earlier that there are some targets that are “private” and basically not configurable for an event session. What are these targets? That part is easy enough to figure out. Let’s adapt the previous script just a bit.

SELECT DISTINCT xo.name AS TargetName
,xo.description AS TargetDescription
,xo.capabilities_desc 
FROM sys.dm_xe_objects xo
WHERE xo.object_type = 'target'
AND (xo.capabilities & 1 = 1);

And here is the list of those private targets:

For those that are Profiler junkies and love to watch events stream in “live”, Extended Events has just the target to do that. The event_stream “private” target, attached to the event session by default and not configurable, will allow one to watch events as the trigger. By now, you have likely also noted that there are a bunch of targets listed with the term “audit” in the name. I will dive into this more in the future, and at that time it will become more apparent why it is not directly configurable through the creation of a user defined Extended Event session.

Stay tuned for future articles where I will go into the use of these various targets and how to attach them to a Session.