sys.dm_xe_objects – Day 56 – One DMV a Day

Hello Geeks and welcome to the Day 56 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday I have started with extended events DMVs. I have talked about the usage of extended events and how important it is to start using them. I have started with the hierarchy and seen sys.dm_xe_packages. Today I am going to cover sys.dm_xe_objects.

Sys.dm_xe_objects will give the objects exposed in extended events. They are classified as following. The first five are documented in MSDN but the last two are undocumented.

Object_Type = Action:

These are run when an event fires and are synchronous. It can add to an event. Collectiong certain details like session ID, plan Handle are few an example.

SELECT xp.name AS PkgName,
	 xo.name AS ObjName,
	 xo.description,
	 xo.object_type,
	 xo.type_name
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
WHERE xo.object_type = 'action'

Object_Type = Event:

A specific interest point in the execution path which exposes certain data about that point in time. An event can be anything similar to error raised, transaction start or end, etc.

SELECT xp.name AS PkgName,
	 xo.name AS ObjName,
	 xo.description,
	 xo.object_type,
	 xo.type_name
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
WHERE xo.object_type = 'event'

Object_Type = Target:

Targets are the objects which consume any event data. They can be synchronous asynchrounous or system provided thread. They include objects like histogram, ring buffer, etc.

SELECT xp.name AS PkgName,
	 xo.name AS ObjName,
	 xo.description,
	 xo.object_type,
	 xo.type_name
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
WHERE xo.object_type = 'target'

Object_Type = Predicate:

Predicates are objects used for comparison and filtering while collecting events data. There are two types of predicates. Pred_source are used for filtering on global state data elements. This can be direct filtering on a scheduler id or task execution time. Pred_compare are used for comparison of an element to a data value like text. They can include like compare of a string.

SELECT xp.name AS PkgName,
	 xo.name AS ObjName,
	 xo.description,
	 xo.object_type,
	 xo.type_name
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
WHERE xo.object_type LIKE 'pred%'

Object_Type = Type:

Type is the description of the data type like the length and other properties. You can see what I mean from the below output.

SELECT xp.name AS PkgName,
	 xo.name AS ObjName,
	 xo.description,
	 xo.object_type,
	 xo.type_name
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
WHERE xo.object_type = 'type'

Object_Type = Map:

Maps are useful to get a meaningful value out of an output. I will be covering sys.dm_xe_map_values the coming blogs in the series. For example, you will see a transaction state returned as a numeric value. To make sense out of that numeric value you have to map it with a more meaningful value.

SELECT xp.name AS PkgName,
	 xo.name AS ObjName,
	 xo.description,
	 xo.object_type,
	 xo.type_name
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
WHERE xo.object_type = 'map'

Object_Type = Message:

As the name suggests it is the message out of the event. You can more sense out from the below output.

SELECT xp.name AS PkgName,
	 xo.name AS ObjName,
	 xo.description,
	 xo.object_type,
	 xo.type_name
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
WHERE xo.object_type = 'message'

Now you can see how many objects are exposed through extended events using sys.dm_xe_objects. Try these queries out on various versions. You will see new objects added with every release.

Tomorrow I will be covering another extended events related DMV. So stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook