Counting Events

After reading through the articles about the pair_matching target (here) and the article on countingthe histogram target (here), this one is going to seem very basic. To be honest, I would consider this to be basic even if it was discussed prior to the two just mentioned. This article will be diving into the shallow depths of the event_counter target.

There is no real surprise to what the function of this target is – to count. The configuration will be very basic in nature. Reading the target will be very basic in nature.

The real use here would be to count event occurrences at a step below the histogram target. Unlike the histogram, this target does not group events. It just counts. You may be asking why use this particular target. That would be a valid question.

Imagine that you just want to know how many times an event has fired but don’t want to add the full overhead of event collection. If an event fires frequently, you can get an idea as to the workload. In addition, you could determine a focus area for troubleshooting at some future point. That could come in handy!

Counting Events

Following the established pattern, before I proceed with trying to configure a session to use this target, I really need to know more about the target. I need to know what I can configure for this target. So, like in previous articles, I just need to query the metadata to find any configurable settings for the event_counter target. The following script will help me do that:

From this query, I now know that I have absolutely nothing to configure that is specific to the target. This reinforces the basic nature of the target.

Now comes the hard part. To effectively use this target, I really should know just what I want to count. Once I get a list of events I want to count, then I need to throw those together into a session. With that in mind, I have the following session with a handful of events that I want to start counting:

After creating that session and letting it run for a little bit in my sandbox, then I am ready to take a peek at the session data. The query to peek into the session data, at a basic level, is just like the query that has been used over the past few articles.

And the results of that query would look something like the following:

event_targetdata

Similar to the simplicity in configuration for the target, the data is stored in a very simple manner. In the target, I will only see a count for the event if the event has fired during the session. Otherwise, the entire event node for the event in question will be missing and just the parent package of the event will exist in the data. Notice that there is a node for every package within the Extended Events engine. Most have no events in this session. Had an event for the package been in the session and not fired, I may see something like the following (which happens to be this same session just prior to a deadlock occurrence):

counter_nodeadlocks

This is handy information and seems to make sense. If an event never fires, why have a node for the data for that event in the session. Sadly, the logic for the packages is a bit reversed. All packages are included regardless. That said, having a placeholder already there can make it a little more efficient for the firing event to be recorded into the target.

With a solid (maybe semi-solid) understanding of what the structure of the target data is, it is time to start parsing the target data into something a tad easier to read and understand.

This will give me the following results in my sandbox:

count_query_out

As is shown in the results, I have results for each of the events specified in the Session. These are the same events and event counts seen in the XML, just in a more friendly format. Most packages have no data due to a lack of events from that package being included in the session.

For this particular server, the only thing that may be of interest is the deadlock occurrence. But since that was caused by a controlled experiment that forced the deadlock, I won’t worry about it. Had this been a production environment, the deadlock may need to be investigated.

Looking a little closer at the query to parse the session data, one could see that it seems rather kludgy. There is probably a better way of parsing the XML, but this was the method I could get to work with my lack of XML / Xquery ability. If you happen to know of a better way to get that data properly correlated from Xquery, I would welcome that in the comments.

In this article I have just shown how to use and configure the event_counter target. In addition, I explained how to get to the data trapped into this target. Lastly, I demonstrated how to find the pertinent information for the target configurations.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.

 

1 Comment - Leave a comment
  1. […] may need to count events in SQL Server. This article by Jason Brimhall helps us understand the process, with examples, in this post from […]

Leave a comment

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










Calendar
October 2015
M T W T F S S
« Sep   Dec »
 1234
567891011
12131415161718
19202122232425
262728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Monday, March 27, 2017