Hello, Thank you so much for responding for my thread. I am going to share my thoughts here out loud so that someone can guide me in the right direction. To start with, This is what I wrote. Trick is the CROSS APPLY.

DECLARE @xml XML

SELECT @xml = CAST(dt.target_data AS XML)

FROM sys.dm_xe_session_targets dt

JOIN sys.dm_xe_sessions ds ON ds.Address = dt.event_session_address

JOIN sys.server_event_sessions ss ON ds.Name = ss.Name

WHERE dt.target_name = 'ring_buffer'

AND ds.Name = 'EE_LongRunningQueries'

SELECT

Events.Info.value('@name', 'VARCHAR(MAX)'),

Events.Info.value('@timestamp', 'DATETIME'),

Datas.ChildInfo.value('@name', 'VARCHAR(MAX)'),

Datas.ChildInfo.value('value[1]','VARCHAR(MAX)')

FROM @xml.nodes('/RingBufferTarget/event') AS Events(Info)

CROSS APPLY Events.Info.nodes('*') AS Datas(childInfo)

If you have any better thoughts, please share with me.

Reading few blogs online on EEs made me realize how power EE is. I want to use it more extensively for monitoring. Currently, EE in my hand is like tool with a fool.

Driving the discussion further X-query -

I want to add some more information so that you can guide me.

What we have today:

we query exec_requests and sessions and get the list of queries running in our system every 1 minute. If anything is running more than 1 minute, we will alert ourselves and get hold of developer to fix the code or add indexes. We also have a config table to exclude certain legacy stuff and maintenance jobs.

What we want to do:

Firstly, I want to know, if our current monitoring system for the sqls running in our system is good or not. Also, we want to use extended events(EE) to monitor our system. the vision I have in my mind is to grab everything that is running on the system through EE, and have a job to wake up every minute look back 1 minute based on timestamp and alert us if there is no sql_statement_completed event is not associated with that session id. This way, I know I am not missing any queries within that 1 minute window. Please let me know if my vision is in the correct path.

Also, I want to know, what else, I can do with EEs. I have plans of collecting dead lock reports. logical drive counters(I know Johnathan has a ticket opened for this. waiting for that ticket to be addressed.)

Please help the fool to use the tool!