Identify long-running queries

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

1. Create an extended events session:

   /*
   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

   • ADD EVENT sqlserver.sql_statement_completed causes an event to fire whenever a single SQL statement¹⁾ 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).

2. Run some queries

3. Inspect the event data:

   ;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

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.

• The sys.dm_xe_sessions DMV returns information about active extended events sessions.

  SELECT *
  FROM sys.dm_xe_sessions

• Useful extended events objects