Over the course of the past couple of articles, I have shown some interesting if not useful functionality in the user interface for working with Extended Event log files. Those articles on merging files and customizing the view can be read here and here.
Today, I am going to explore how to further customize the view of the GUI for working with Extended Event Log files or XEL files. This further customization does include working with filters. Prior to proceeding, it would be beneficial to review the aforementioned articles – they do outline some building blocks for this article.
Sifting through it all
Even with a well tuned XE session, sometimes there is just a deluge of data. A deluge of data can make it more difficult from time to time to find exactly what is the most likely candidate to be the cause of the problem currently at hand (whatever that may be).
When dealing with a large quantity of data, it becomes necessary to filter that data down. Filtering it down will make it significantly easier to handle – when an appropriate filter is utilized. The GUI permits the implementation of filters in a couple of different ways to help with these dives into the XE logged data.
When convinced to use the GUI to peruse the log data, there are a few possibilities in how to create a filter (and yes it would be better to do it with a script). The first of these methods is actually quite simple. Let’s start with a grid display of the same session used in the previous couple of articles:
From here, if I right click one of the cells as shown below, I will be prompted with a context menu:
By right clicking a value in the grid, an option to “Filter by this value” will be given in the context menu. Selecting that option will open a new window allowing for further configuration of the filter or to just accept the filter as-is.
From the “Filters” window, you can see there is the option to set a time based filter, to create a filter on values for the fields within the trace log or to combine the two. This is pretty straight forward here. That said, recall that filters configured here can be saved in the viewsetting file discussed in the prior article (mentioned at the beginning of this article).
Further Analysis
After filtering down the results, what if you wish to now perform more complex analysis? Maybe the analysis is to be done for trending purposes, or maybe something else. Regardless of the purpose, the need is there and you wish to know how to do it (but for some reason have a serious allergen against doing the work via a script). Luckily there just happens to be a magic button within the GUI that helps to perform this task.
Within the GUI, we are given the opportunity to group and aggregate data. The aggregations cannot be performed without first performing a grouping (very similar to the requirements via script). The grouping can contain multiple fields or just a single field. Let’s take a quick peek.
Clicking on the Grouping button, the following window opens, permitting the configuration of the groups.
This looks pretty familiar and standard. It is rather simple in design and function. Move columns from left to right to add to the grouping. To remove from the grouping, move from right to left.
With the group established, then comes the fun part to help with analysis. This is where aggregations comes into play. Within an aggregation; we can perform counts, max, min, avg and sum. Depending on the field, the aggregation that can be performed may be restricted. Clicking the aggregation button, one will see the following window:
Given the session being evaluated and the columns that have been chosen previously, I am restricted to the columns shown in the preceding image. Aggregations can only be performed on columns selected in the “Column Chooser”. The rest of the configuration of the aggregation is merely an exercise in plug and play to determine which fields or aggregation will serve your needs the best.
For this exercise and the chosen grouping, I am going to go with Average on the duration field and then sort the aggregation by that same field in a descending order. For your purposes, feel free to choose something more meaningful or appropriate to your needs.
Cautionary Tale
If there happens to be a rather large number of events within the session file(s) being evaluated, don’t expect this to work without a hitch or three. Trying to load a 1GB trace file (read that as a default size on an XE trace file) with over 600,000 events, SSMS regularly crashed on me while trying to apply a filter, a group, and even the aggregation. None of this has been an issue while trying to perform similar types of groupings and aggregations with large trace files via script.
If you are going to attempt to manipulate aggregations, groupings, filters etc. via the GUI with large trace files, understand that you may run into errors and crashes when using the graphical interface rather than a script. The choice is yours!
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.
