sys.dm_xe_session_object_columns – Day 60 – One DMV a Day

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

Since I started this series has come a long way and is completing two months. This is twice of any one day series so far. This is like a double century in cricket. I feel like Sachin Tendulkar, the god of cricket, when he did the first double century in first class cricket. Enough of cricket and let’s get to today’s blog. I will be covering sys.dm_xe_session_object_columns.

Yesterday we have seen sys.dm_xe_sessions which gives a single row with properties of a session. Sys.dm_xe_session_object_columns gives the configuration values set for each object bound to the session. Let us create the below session and check the output of sys.dm_xe_session_object_columns.

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'SSG_WaitSession_OneDMVaDay')
    DROP EVENT SESSION SSG_WaitSession_OneDMVaDay ON SERVER
GO

CREATE EVENT SESSION SSG_WaitSession_OneDMVaDay ON SERVER

ADD EVENT [sqlos].[wait_info],
ADD EVENT sqlserver.sp_statement_completed 
	 (SET collect_object_name = (1), 
	  collect_statement = (0)
	 ),
ADD EVENT sqlserver.sql_batch_completed

ADD TARGET [package0].[histogram]
(
    SET filtering_event_name=N'sqlos.wait_info',
	source_type = 1,
    source = N'package0.callstack'
)
WITH
(
    MAX_MEMORY = 50 MB,
    MAX_DISPATCH_LATENCY = 5 SECONDS)
GO

ALTER EVENT SESSION SSG_WaitSession_OneDMVaDay ON SERVER STATE = START;
GO

Now running select on sys.dm_xe_session_object_columns will get us the below result.

SELECT xs.name,
	 xsoc.column_name,
	 xsoc.column_value,
	 xsoc.object_type,
	 xsoc.object_name
FROM sys.dm_xe_session_object_columns xsoc
INNER JOIN sys.dm_xe_sessions xs
ON xsoc.event_session_address = xs.address
WHERE xs.name = 'SSG_WaitSession_OneDMVaDay'

From the above results you can see the values set for each configurable columns in the session. If you observe correctly I have set the configurable object columns collect_object_name and collect_statement to non-default Boolean values. The same is shown in the output from sys.dm_xe_session_object_columns highlighted columns.

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