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.

SELECT oc.object_name AS ObjectName
,oc.name AS column_name, oc.type_name
,oc.column_type AS column_type
,oc.column_value AS column_value
,oc.description AS column_description
FROM sys.dm_xe_object_columns oc
WHERE oc.column_type <> 'readonly'
ORDER BY ObjectName,column_name;
GO

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.

SELECT DISTINCT xo.object_type AS XEComponentWithSchma
FROM sys.dm_xe_object_columns oc
INNER JOIN sys.dm_xe_objects xo
ON xo.name = oc.object_name
AND xo.package_guid = oc.object_package_guid;
GO

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.

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.

SELECT oc.object_name AS ObjectName
,oc.name AS column_name, oc.type_name
,oc.column_type AS column_type
,oc.column_value AS column_value
,oc.description AS column_description
,xo.object_type
--,xo.description AS ObjDescription
FROM sys.dm_xe_object_columns oc
INNER JOIN sys.dm_xe_objects xo
ON xo.name = oc.object_name
AND xo.package_guid = oc.object_package_guid
WHERE oc.column_type <> 'readonly'
AND oc.object_name NOT IN (
SELECT name
FROM sys.dm_xe_objects xo
WHERE xo.object_type = 'event'
OR xo.object_type = 'target')
ORDER BY ObjectName,column_name
GO

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:

SELECT DISTINCT oc.object_name AS ObjectName
,xo.object_type
FROM sys.dm_xe_object_columns oc
INNER JOIN sys.dm_xe_objects xo
ON xo.name = oc.object_name
AND xo.package_guid = oc.object_package_guid
WHERE oc.column_type <> 'readonly'
AND xo.object_type = 'map'
AND oc.object_name IN (
SELECT name
FROM sys.dm_xe_objects xo
WHERE xo.object_type = 'event')
ORDER BY ObjectName
GO

And the results:

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.

DECLARE @objtype VARCHAR(128) = 'map' --'map','target','event', null for all
,@datatype VARCHAR(32) = NULL --'data','readonly','customizable'
,@excludetype VARCHAR(32) = 'readonly' --'data','readonly','customizable', 'ALL' if no exclusions
SELECT oc.object_name AS ObjectName
,oc.name AS column_name, oc.type_name
,oc.column_type AS column_type
,oc.column_value AS column_value
,oc.description AS column_description
,xo.object_type
FROM sys.dm_xe_object_columns oc
INNER JOIN sys.dm_xe_objects xo
ON xo.name = oc.object_name
AND xo.package_guid = oc.object_package_guid
WHERE oc.column_type = ISNULL(@datatype,oc.column_type) 
AND xo.object_type = ISNULL(@objtype,xo.object_type)
AND oc.column_type <> @excludetype
ORDER BY ObjectName,column_name
GO

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.