Microsoft SQL 2012 Extended Event - Long Running Queries

LongRunningQueries.sql

--SQL 2012 Extended Event

CREATE EVENT SESSION [Long_Running_Queries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(package0.collect_cpu_cycle_time,package0.collect_system_time,sqlos.task_time,sqlserver.plan_handle,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[greater_than_int64]([duration],(2000000)) AND [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'INSERT_DB_NAME_HERE')))
ADD TARGET package0.ring_buffer(SET max_memory=(131072))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

WITH RingBuffer AS
(SELECT CONVERT(XML, target_data) AS xml_data, *
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'Long_Running_Queries')
SELECT
events_xml.value('(@timestamp)[1]', 'varchar(50)') AS Timestamp,
REPLACE(events_xml.value('(action[@name="sql_text"]/value)[1]', 'varchar(MAX)'), '\n', '') AS Query
FROM RingBuffer
CROSS APPLY xml_data.nodes('//event') n (events_xml)
WHERE events_xml.value('(@name)[1]', 'varchar(100)') = 'sql_statement_completed'
ORDER BY 1 DESC