Extended Events and Fixed Schemas

, 2015-09-14

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.

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.

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:

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.

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads