Extended Events and Fixed Schemas

Working with databases, XML, and many software applications, it is not uncommon to deal with fixed schemas. In the database world, it is fairly fundamental to have some sort of schema. The schema represents the typing of the bit-strings that are being stored or presented. One might even call it the template.

With the fundamental nature of schemas within a database, it should come as no surprise that Extended Events also has a means to expose the schema of many of the components that can be found within the XE engine. To access the stored schemas, one would use the DMV, sys.dm_xe_object_columns.

This DMV exposes the fixed schema that is available for the object/component for the different data groups. If you recall from the previous article, there are three data classifications (readonly, customizable, and data) that could be present for a particular object.

Not all objects have a fixed-schema that will be exposed through this DMV. In fact, the types of objects that will be exposed are the target, map and event object types. When considering the different components, this stands to reason since many objects infer a single point of data and not necessarily a schema (we will be discussing those other components in the near future).

Schemas

The exploration of the schemas tied to the different objects is pretty straight forward. Let’s begin with the most basic version. I will not focus much on what is exposed in the DMV since this is basically the schema and that concept should be fairly straight forward to most DBAs. For instance, a schema will contain a basic model of the the columns and data types and relationships. The schemas in XEvents is very similar.

This being a basic query to look at the schemas of the components available in extended events, it should be plain to see that nothing is being excluded from the results. This means we could be seeing the schema for both events and targets. Recall that there are only three types of objects which have stored schemas.

 

Note here that I have referenced back to the sys.dm_xe_objects DMV, This is done to ensure I can get the object types. That piece of info needs to come from the xe_objects DMV. This DMV will be used throughout the remaining examples too.

xecomponents_wschema

Let’s complicate things just a little bit. If I try a little more complex query and want to eliminate the target and event schemas as shown in the following, I will get an interesting result.

This will return no results. Now, there should be something returned since I should see some map schemas. Yet, this query returns nothing. As it happens, there are maps that share the same name as some of the events.

I can see that little piece of fun by altering my query to the following:

And the results:

dupxe_maps_names

This is good information to know if one of these events that shared a name with a map was needed for a specific monitoring solution being explored. It is easy enough to code around, but it can be just a bit of a nuisance if the name duplication remained unknown.

Taking this information into account, I can now query the schemas more precisely and get a good feel for what kind of schema is going to be available for the component.

With this type of query, I can now query the schemas in a better way. I can look at all of the data groups, or a specific data group. I can also look for a specific object type / component. This type of query will become an essential building block as we get into trying to figure out which object will best fit the needs of the problem being investigated or the trace desired to be created.

Stay tuned for more articles as we prepare to discuss actions, targets and data types.

1 Comment - Leave a comment
  1. […] Extended Events and Fixed Schemas September 14, 2015 […]

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


Welcome , today is Thursday, March 23, 2017