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 statement1) 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

1)
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