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.

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.

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.

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.

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.

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.

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.

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,

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