====== Identify long-running queries ======
Here's how to identify long-running queries using extended events.

  -- Create an extended events session:
  .. <code tsql>/*
ALTER EVENT SESSION QueryMonitor ON SERVER STATE = STOP
DROP EVENT SESSION QueryMonitor ON SERVER
--*/
 
CREATE EVENT SESSION QueryMonitor ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
  ACTION(
    sqlserver.session_server_principal_name
  )
  WHERE duration > 5000000  -- microseconds
)
ADD TARGET package0.ring_buffer
WITH (
  EVENT_RETENTION_MODE = NO_EVENT_LOSS
, MAX_DISPATCH_LATENCY = 1 SECONDS
)

ALTER EVENT SESSION QueryMonitor ON SERVER STATE = START
</code>
    * ''ADD EVENT sqlserver.sql_statement_completed'' causes an event to fire whenever a single SQL statement((For monitoring batch completion, the event ''sqlserver.sql_batch_completed'' was introduced in SQL Server 2012. Note that in this case you may want the full batch SQL text rather than the SQL //statement// included in the default output -- you can specify this in the ''ACTION'' clause as ''sqlserver.sql_text'')) completes.
    * The ''ACTION'' clause adds a user identifier to the output (not included by default).
    ** The ''WHERE'' clause restricts the recording of events to those where the SQL statement's execution duration exceeds five seconds (specified in **microseconds**).
  -- Run some queries
  -- Inspect the event data:
  .. <code tsql>;WITH cte AS (
  SELECT CAST(xet.target_data AS XML) AS TargetData
  FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xe ON xe.[address] = xet.event_session_address
  WHERE xe.name = 'QueryMonitor'
), shred AS (
  SELECT
    ev.value('(@timestamp)[1]', 'DATETIME') AS QueryEndTime
  , ev.query('(data[@name="statement"])').value('(//data/value)[1]', 'NVARCHAR(MAX)') AS SqlStatement
  , (ev.query('(data[@name="duration"])').value('(//data/value)[1]', 'BIGINT') + 500 )/ 1000 AS DurationMillis
  , ev.query('(action[@name="session_server_principal_name"])').value('(//action/value)[1]', 'NVARCHAR(MAX)') AS UserName
  FROM cte
    CROSS APPLY cte.TargetData.nodes('//RingBufferTarget/event') n(ev)
)
SELECT 
  DATEADD(MILLISECOND, -DurationMillis, QueryEndTime) AS QueryStartTime
, QueryEndTime
, (DurationMillis + 500 )/ 1000 AS DurationSecs
, UserName
, SqlStatement
FROM shred
ORDER BY QueryEndTime DESC</code>

Data for your event session is returned from ''sys.dm_xe_session_targets'' as a single XML field -- you can see this by executing the CTE definition alone -- which must then be shredded. Shredding can be slow, so an appropriately-restrictive ''WHERE'' clause in the event session definition is important to limit the amount of XML generated.

===== Further information =====

  ** The ''sys.dm_xe_sessions'' DMV returns information about active extended events sessions.
  .. <code tsql>SELECT *
FROM sys.dm_xe_sessions</code>
  * [[admin:useful_extended_events_objects]]
{{tag> extended_events statistics admin}}