sys.dm_xe_object_columns – Day 57 – One DMV a Day

Hello Geeks and welcome to the Day 57 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.

After sys.dm_xe_objects its time to see another DMV which gives the objects’ schema. Sys.dm_xe_object_columns provides the columns available in each object. This helps you know what columns will be returned when I collect data for an event. You can then further use these columns for filtering.

Sys.dm_xe_object_columns provides the column type too. The type of the column can be readonly, data or customizable. Readonly type are like the header or system metadata about the event. They are mostly static. Hence you will see a static value for the column. Data are the columns which contain the information returned when the event is fired. This is mostly the data you will need when you collect an event. Customizable columns are the additional data you may want to collect. Let us see the output below to understand these.

SELECT xoe.name AS colName,
	 xoe.description AS colDesc,
	 xoe.object_name AS objName,
	 xoe.type_name AS objType,
	 xoe.column_type AS colType,
	 xoe.column_value AS colValue
FROM sys.dm_xe_object_columns xoe
WHERE xoe.object_name LIKE 'sql_statement_completed'

sys.dm_xe_object_columns

In the above output I have queried the columns for sql_statement_completed from sys.dm_xe_object_columns. You can observe that columns like UUID, VERSION, etc., are readonly. They have a static value for column_value. The last part of result set contains the data columns which are collected by default.

The two columns in between are customizable. You can choose to collect or not collect the statement or the statement plan handle. Adding additional columns will add a little overhead. But these are useful in few troubleshooting cases.

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

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 →

One Comment on “sys.dm_xe_object_columns – Day 57 – One DMV a Day”

Leave a Reply

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