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'

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