The last few articles have covered various topics for the event_file and ring_buffer target. The topics range from basic queries in how to extract the session data on up through best practices. The fun doesn’t end there!

I will be covering more on the different types of targets over the next several articles. Before diving into the remaining Targets, it would be very beneficial to take a look at yet another core concept. Rather, it is time to return to a core concept and look at it from a different angle.

I would like to revisit the base principle around the Set Operations or configurable fields (recall that some settings are stored in the *_fields object). The remaining targets have a handful of settings to configure. Some of these settings are required while others should be required.

Know Before you GO Target Settings

When adding a Target to a session, a configurable setting may or may not be obviously available for use with the Target. In and of itself, this can be mildly frustrating if the wrong settings are tried with the wrong Target. More frustrating is that some settings are required. While the required setting may be logically deduced, it does not always work out that way.

This frustration can be mitigated through the use of the GUI to create the session. This introduces its own set of problems as was discussed previously. If the session is to be created through script, then the settings available to the Target won’t be presented in a nice friendly fashion like the GUI. So, what can one do?

The answer lies within the metadata for Extended Events. I have shown through several articles how to expose the metadata for working with Extended Events. Exposing the available settings for each of the Targets is not much different from the previous explorations into the metadata.

The first step is to recall that the Targets are objects within Extended Events. Building from that base, the metadata for the Target settings has to be stored somewhere. Clue in that the setting is stored as a Field (or Column) after the session is started, and I can safely presume that the settings would be exposed through the sys.dm_xe_object_columns DMV. With that in mind, my query would look like the following:

DECLARE @includeprivate TINYINT = 1 --1 public only and 2 for all
SELECT xoc.object_name AS TargetName
,xoc.name AS FieldName
, xoc.capabilities_desc
,xoc.description
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_object_columns xoc
ON xo.package_guid = xoc.object_package_guid
AND xo.name = xoc.object_name
WHERE xo.object_type = 'target'
AND (xo.capabilities IS NULL
OR xo.capabilities & 1 <> @includeprivate)
ORDER BY xoc.object_name;

This would produce the following results:

A couple of notes on the results and the query are necessary now. By querying the metadata from script, a description of the setting can be exposed. This leads to a better understanding of the intent and purpose of the setting. Think about the occurrence_number field for a moment. Just going by the name, it would be difficult to understand what this setting actually does. As it turns out, it is a directive to the XE Engine to store only the specified number of event occurrences for each event. In addition to a better definition of the setting, I can expose which fields are mandatory. The data for the mandatory fields is contained within the capabilities_desc column.

While this is great and useful information, it does not resolve all of the hair tugging just yet. It could be presumed that since the setting exists it has (or might have) a default value. Especially taking into consideration that there are very few mandatory settings. The presumption on the defaults is quite accurate because there are default values for these settings. Looking through the DMV, there is no column that appears to be the source for these “defaults”. Unless of course you recall from prior articles that there is the column_value column which will hold the default value for these settings. With that, I can now adjust my query to also show me the default settings as follows:

DECLARE @includeprivate TINYINT = 1 --1 public only and 2 for all
SELECT xoc.object_name AS TargetName
,xoc.name AS FieldName
, xoc.capabilities_desc
,xoc.description
, xoc.column_value AS DefaultSetting
, xoc.type_name AS DataType
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_object_columns xoc
ON xo.package_guid = xoc.object_package_guid
AND xo.name = xoc.object_name
WHERE xo.object_type = 'target'
AND (xo.capabilities IS NULL
OR xo.capabilities & 1 <> @includeprivate)
ORDER BY xoc.object_name;

This will show me a result set very similar to the prior results. The main difference being that I am now including the default value. I want to highlight a couple of these defaults:

Anybody familiar with SQL Server defaults should know that a default value of 0 should equate to “unlimited”. If you decide to use the ring_buffer target and do not specify a value for max_memory, you have just given free rain to the XE Engine to use as much memory as physically possible on the server.

Leaving the max_memory setting at its default happens to be dangerous enough that it could cause undue memory pressure and, in extreme cases, cause SQL Server to grind to a halt. This underscores the need to understand what the configuration options are and what they represent. The default values may be fine for many workloads, but really should be evaluated just as should be done with all defaults within SQL Server.

As I mentioned, this is an important topic to discuss at this point prior to more discussions on the different Targets. I will use this type of query throughout the next few articles to help illustrate the settings for the Targets as well as discuss how to get to the target data.

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.