Storing Event Data

, 2015-09-16

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.”target 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.


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:

		,xo.description AS TargetDescription
	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.

		,xo.description AS TargetDescription
	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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads