-- Extended Event for finding *long running query*
 IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRunningQuery')
 DROP EVENT SESSION LongRunningQuery ON SERVER
 GO
 -- Create Event
 CREATE EVENT SESSION LongRunningQuery
 ON SERVER
 -- Add event to capture event
 ADD EVENT sqlserver.sql_statement_completed
 (
 -- Add action - event property
 ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
 -- Predicate - time 1000 milisecond
 WHERE sqlserver.sql_statement_completed.duration > 1000
 )
 -- Add target for capturing the data - XML File
 ADD TARGET package0.asynchronous_file_target(
 SET filename='c:\LongRunningQuery.xet', metadatafile='c:\LongRunningQuery.xem'),
 -- Add target for capturing the data - Ring Bugger
 ADD TARGET package0.ring_buffer
 (SET max_memory = 4096)
 WITH (max_dispatch_latency = 1 seconds)
 GO
 -- Enable Event
 ALTER EVENT SESSION LongRunningQuery ON SERVER
 STATE=START
 GO
 -- Run long query (longer than 1000 ms)
 SELECT *
 FROM AdventureWorks.Sales.SalesOrderDetail
 ORDER BY UnitPriceDiscount DESC
 GO
 -- Stop the event
 ALTER EVENT SESSION LongRunningQuery ON SERVER
 STATE=STOP
 GO
 -- Read the data from Ring Buffer
 SELECT CAST(dt.target_data AS XML) AS xmlLockData
 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 = 'LongRunningQuery'
 GO
 -- Read the data from XML File
 SELECT event_data_XML.value('(event/data[1])[1]','VARCHAR(100)') AS Database_ID,
 event_data_XML.value('(event/data[2])[1]','INT') AS OBJECT_ID,
 event_data_XML.value('(event/data[3])[1]','INT') AS object_type,
 event_data_XML.value('(event/data[4])[1]','INT') AS cpu,
 event_data_XML.value('(event/data[5])[1]','INT') AS duration,
 event_data_XML.value('(event/data[6])[1]','INT') AS reads,
 event_data_XML.value('(event/data[7])[1]','INT') AS writes,
 event_data_XML.value('(event/action[1])[1]','VARCHAR(512)') AS sql_text,
 event_data_XML.value('(event/action[2])[1]','VARCHAR(512)') AS tsql_stack,
 CAST(event_data_XML.value('(event/action[2])[1]','VARCHAR(512)') AS XML).value('(frame/@handle)[1]','VARCHAR(50)') AS handle
 FROM
 (
 SELECT CAST(event_data AS XML) event_data_XML, *
 FROM sys.fn_xe_file_target_read_file
 ('c:\LongRunningQuery*.xet',
 'c:\LongRunningQuery*.xem',
 NULL, NULL)) T
 GO
 -- Clean up. Drop the event
 DROP EVENT SESSION LongRunningQuery
 ON SERVER
 GO