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'

sys.dm_xe_objects 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'

sys.dm_xe_objects 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'

sys.dm_xe_objects 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%'

sys.dm_xe_objects Predicate

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'

sys.dm_xe_objects 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'

sys.dm_xe_objects 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'

sys.dm_xe_objects 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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *