Storing Event Data

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.

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:

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

xe_public_targets

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.

xe_target_compare

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.

And here is the list of those private targets:

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

No Comments - Leave a comment

Leave a comment

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










Calendar
September 2015
M T W T F S S
« Aug   Oct »
 123456
78910111213
14151617181920
21222324252627
282930  
Content
SQLHelp

SQLHelp

  • @live_sql: #sqlhelp Is there a way to allow users attach DB's using the GUI and them not being SA. I crested custom server roles but still no GUI?
  • @joachimpr: Are your reports at work always taking forever to show you data? This could be the reason why! #SQLServer #sqlhelp
  • @NickyvV: Did anyone ever find a solution for memory consumtion of PP in Excel 2010 that @Kjonge made for 2013 and SSAS? #sqlhelp #ssashelp
  • @Ranjeeth: Can I have DBSer01, DBServ02 having replicas in DBServ03(2 AGs, diff clusters one target). #sqlhelp #alwayson trying to save testing time.
  • @jdanton: @Sir_NiN_Sir both free, but dev=enterprise, feature wise, express, much more limited. For learning, I’d go dev, or just use Docker. #sqlhelp
  • @Sir_NiN_Sir: How's different MSSQL Developer edition and Express edition? both edition for free licensed or not? If I need to practice t-sql.#sqlhelp
  • @jdanton: @Spinner_ Nope. Added a bunch of features to it, as it of SP1 2016. #sqlhelp
  • @Spinner_: #sqlhelp are Microsoft's deprecating sql server express?
  • @SQLHA: @ke0mms Would need to know about your issue before I could even think about giving an answer. #sqlhelp
  • @SQLHA: @ke0mms What do you mean by availability server? That's not a "thing" :) #sqlhelp

Welcome , today is Wednesday, July 26, 2017