Histograms and Events

histogramThe last few articles covered the pair_matching target and some uses and details that go along with it. For a recap, you can read here.

The pair_matching target was just one of the more advanced targets available for use via Extended Events. In this segment, I will be covering another of the more advanced targets – the histogram target.

The histogram target is a target that provides grouping of a specific event within the event session. This group can be configured to be on a data point within the payload of the event session or on an action that has been attached to the event. That said, careful consideration should be given to what the grouping configuration will be.

Histograms and Events

In principle, this is fine and well. As is often the case with Extended Events, it is often more clear to see how this works. The first step to seeing this in action is to figure out what the configurable options are for the histogram target.

From this query, that I have shown a few times now, I can see  the following results:


The histogram target has four configurable options. Note that none of the configurations is mandatory and two have default values assigned. Even though none of the configurations is mandatory, I would say that the source is somewhat mandatory. This is the field (or action) on which to group the data. In addition, I would also say that the filtering_event_name is a mandatory field, unless there is only one event in the session. Even at that, I would recommend getting into the habit of setting a value for this configuration.

In addition to the lack of a mandatory configuration, I want to make particular note of the source_type configuration. This configuration has a default setting of 1. This value correlates to “action_name” as a grouping field type. Keep that value in mind for later recall. In the interim, I have a script that will create an XEvent Session to track deadlocks occurring on the instance. This will be used for the remainder of the demos.

In this session, I have decided I want to trap occurrences of deadlocks to two different targets; the event_file target and the histogram target. On the histogram target, I have configured the filtering_event_name, source_type and source settings. Now, I want to create a second session that I will not start. This additional session will be strictly to show the differences between settings values.

Now with both sessions deployed, I will query the sys.server_event_session_fields view to find what configuration settings are in effect for these particular sessions.


Despite the default value for source_type according to the metadata, it really seems there is no default value based on these results. Keep that in mind when building this target into a session. Much like the filtering_event_name, I recommend just setting the source_type to remove all doubt of the value being used for this configuration.

Now that that is understood, go back and clean up the Deadlocksrc session if you executed that script. I will just be using the Deadlock session from this point.

To ensure data has loaded into the target for the Deadlock session, I have created deadlocks a few times in a few different databases. It is important that the deadlocks have been created in different databases due to the configuration of the XEvent Session and the configuration of the histogram target in this session.

I have configured the grouping field to be database_id. In addition, it’s just not as much fun to show a single data point. This session is designed to help me understand which databases are hotspots for deadlocks. Then I can focus my attention on the hotspots and tune from there.

So how do I query the target data? It really is very much like the previous targets. I will once again start by querying the target with a very basic query.

Looking at just the XML, I will see a structure similar to the following (values will differ):


I have highlighted a couple of areas of interest. In blue, I have highlighted the count. The count is maintained for each grouping . This brings me to the section highlighted in green. This is the value – or database_id that I specified in the “source” configuration for this target. Now all I need to do is query the target and get this data into something a little more friendly. Here is how I have done just that:

I have a few options demonstrating the progression of steps for this particular query. First, as I have shown previously, I have dumped the target data into a temp table for improved performance. Then I query the temp table and parse the XML from there.

In the first shot at parsing the data, all I see is a database_id and a count. This is not very helpful unless the mapping of database id to name (or one queries the information manually). Thus the need for the second query where the data is joined to sys.databases to get this data directly in a single query.

For my particular setup, here are my results:


There is an alternative to this method. Rather than group on the event field called database_id, I could group on the action called database_name. Then I would only need to query the session details. I will leave that choice to you to make.

Back to the results (as contrived as they may be), I can see that I have two databases that seem to be the sources of deadlocks on a pretty regular basis. Well, at least in comparison to the remaining 100 databases in my instance. Now, I can filter down the deadlocks I want to pay attention to by querying the event_file and filtering just for deadlocks that occur in either of these two databases. I could also create a session to trap deadlocks specifically for those particular databases rather than monitoring all databases. Again, this is one of those things that just gives me several options were there may be more than one correct choice.

In this article I have just shown how to use and configure the histogram 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.


3 Comments - Leave a comment
  1. […] Histograms and Events October 22, 2015 […]

  2. Mark Freeman says:

    How could I extend the histogram output and the query to read from it if I wanted to get the DeadlockCount by DBName by EventDate?

Leave a comment

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

October 2015
« Sep   Dec »


  • @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