33 — Extended Events (XE)

Package (sqlserver, sqlos, SecAudit)
  └── Event (e.g., sql_statement_completed)
        ├── Actions (data columns appended to every event: sql_text, plan_handle, etc.)
        ├── Predicates (server-side filter: WHERE clause on event fields)
        └── Target (where data goes: ring_buffer, event_file, histogram, etc.)

CREATE EVENT SESSION [session_name] ON SERVER
ADD EVENT sqlserver.event_name
(
    ACTION
    (
        sqlserver.sql_text,
        sqlserver.session_id,
        sqlserver.database_name
    )
    WHERE
    (
        sqlserver.database_name = N'YourDatabase'
        AND duration > 1000000  -- microseconds (1 second)
    )
)
ADD TARGET package0.ring_buffer
(
    SET max_memory = 51200  -- KB (50 MB)
)
WITH
(
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE       = 0,       -- 0 = use MAX_MEMORY
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY      = OFF,
    STARTUP_STATE        = OFF      -- ON = auto-start at SQL startup
);

-- Start the session
ALTER EVENT SESSION [session_name] ON SERVER STATE = START;

ADD TARGET package0.ring_buffer
(SET max_memory = 51200)  -- KB

ADD TARGET package0.event_file
(
    SET filename          = N'D:\XELogs\my_session.xel',
        max_file_size     = 100,    -- MB per file
        max_rollover_files = 5       -- keep 5 files max
)

ADD TARGET package0.histogram
(
    SET filtering_event_name = N'sqlserver.error_reported',
        source_type = 0,           -- 0=event field, 1=action
        source      = N'error_number'
)

-- Filter by database
WHERE (sqlserver.database_name = N'MyDatabase')

-- Filter by duration (microseconds)
WHERE (duration >= 1000000)        -- >= 1 second

-- Filter by session
WHERE (sqlserver.session_id = 55)

-- Filter by error number
WHERE (error_number = 1205)        -- deadlock

-- Exclude system sessions
WHERE (sqlserver.is_system = 0)

-- Compound: duration AND database
WHERE (
    sqlserver.database_name = N'MyDatabase'
    AND duration >= 500000          -- >= 0.5 seconds
    AND sqlserver.is_system = 0
)

-- Start a session
ALTER EVENT SESSION [session_name] ON SERVER STATE = START;

-- Stop a session
ALTER EVENT SESSION [session_name] ON SERVER STATE = STOP;

-- Drop a session
DROP EVENT SESSION [session_name] ON SERVER;

-- Modify a running session (limited changes allowed while running)
ALTER EVENT SESSION [session_name] ON SERVER
    DROP EVENT sqlserver.sql_statement_completed;

ALTER EVENT SESSION [session_name] ON SERVER
    ADD EVENT sqlserver.rpc_completed
    (WHERE (duration > 500000));

-- View all sessions and their state
SELECT name, event_session_id
FROM sys.dm_xe_sessions;

-- Enable blocked process threshold (seconds)
EXEC sp_configure 'blocked process threshold (s)', 5;
RECONFIGURE;

CREATE EVENT SESSION [BlockingCapture] ON SERVER
ADD EVENT sqlserver.blocked_process_report
(
    ACTION
    (
        sqlserver.sql_text,
        sqlserver.database_name,
        sqlserver.session_id,
        sqlserver.client_app_name,
        sqlserver.client_hostname
    )
)
ADD TARGET package0.ring_buffer
(SET max_memory = 51200)
WITH
(
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE        = ON
);

ALTER EVENT SESSION [BlockingCapture] ON SERVER STATE = START;

SELECT
    xdr.value('@monitorLoop', 'int')       AS monitor_loop,
    xdr.value('blocked-process-report[1]/blocked-process[1]/process[1]/@spid', 'int') AS blocked_spid,
    xdr.value('blocked-process-report[1]/blocked-process[1]/process[1]/@waittime', 'bigint') AS wait_ms,
    xdr.value('blocked-process-report[1]/blocked-process[1]/process[1]/inputbuf[1]', 'nvarchar(max)') AS blocked_sql,
    xdr.value('blocked-process-report[1]/blocking-process[1]/process[1]/@spid', 'int') AS blocking_spid,
    xdr.value('blocked-process-report[1]/blocking-process[1]/process[1]/inputbuf[1]', 'nvarchar(max)') AS blocking_sql
FROM
(
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'BlockingCapture'
      AND st.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event/data/value/blocked-process-report') AS XEventData(xdr);

CREATE EVENT SESSION [DeadlockCapture] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
(
    SET filename          = N'D:\XELogs\deadlocks.xel',
        max_file_size     = 50,
        max_rollover_files = 10
)
WITH
(
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE        = ON
);

ALTER EVENT SESSION [DeadlockCapture] ON SERVER STATE = START;

SELECT
    event_data.value('(event/@timestamp)[1]', 'datetime2')       AS deadlock_time,
    event_data.query('(event/data/value/deadlock)[1]')            AS deadlock_graph
FROM
(
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file(
        N'D:\XELogs\deadlocks*.xel', NULL, NULL, NULL
    )
) AS t;

SELECT
    xdr.value('@timestamp', 'datetime2')            AS deadlock_time,
    xdr.query('.')                                  AS deadlock_graph_xml
FROM
(
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
    WHERE s.name = 'system_health'
      AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY deadlock_time DESC;

CREATE EVENT SESSION [LongQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION
    (
        sqlserver.sql_text,
        sqlserver.plan_handle,
        sqlserver.database_name,
        sqlserver.session_id,
        sqlserver.client_app_name,
        sqlserver.username
    )
    WHERE
    (
        duration >= 3000000          -- >= 3 seconds (microseconds)
        AND sqlserver.is_system = 0
        AND sqlserver.database_name <> N'master'
    )
),
ADD EVENT sqlserver.rpc_completed
(
    ACTION
    (
        sqlserver.sql_text,
        sqlserver.plan_handle,
        sqlserver.database_name,
        sqlserver.session_id,
        sqlserver.client_app_name
    )
    WHERE
    (
        duration >= 3000000
        AND sqlserver.is_system = 0
    )
)
ADD TARGET package0.event_file
(
    SET filename          = N'D:\XELogs\LongQueries.xel',
        max_file_size     = 200,
        max_rollover_files = 5
)
WITH (MAX_DISPATCH_LATENCY = 15 SECONDS, STARTUP_STATE = ON);

ALTER EVENT SESSION [LongQueries] ON SERVER STATE = START;

SELECT
    DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()),
        event_data.value('(event/@timestamp)[1]', 'datetime2')) AS event_time_local,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000 AS duration_ms,
    event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') / 1000 AS cpu_ms,
    event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS database_name,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)')      AS sql_text,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') AS app_name
FROM
(
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file(
        N'D:\XELogs\LongQueries*.xel', NULL, NULL, NULL
    )
) AS t
ORDER BY duration_ms DESC;

CREATE EVENT SESSION [RecompileCapture] ON SERVER
ADD EVENT sqlserver.sql_statement_recompile
(
    ACTION
    (
        sqlserver.sql_text,
        sqlserver.database_name,
        sqlserver.session_id,
        sqlserver.plan_handle
    )
    WHERE
    (
        sqlserver.database_name = N'YourDatabase'
        AND sqlserver.is_system = 0
    )
)
ADD TARGET package0.ring_buffer
(SET max_memory = 51200)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = OFF);

ALTER EVENT SESSION [RecompileCapture] ON SERVER STATE = START;

CREATE EVENT SESSION [FailedLogins] ON SERVER
ADD EVENT sqlserver.failed_login
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.server_instance_name,
        sqlserver.username
    )
)
ADD TARGET package0.ring_buffer
(SET max_memory = 10240)
WITH (STARTUP_STATE = ON);

ALTER EVENT SESSION [FailedLogins] ON SERVER STATE = START;

SELECT
    event_data.value('(event/@name)[1]',      'nvarchar(128)') AS event_name,
    event_data.value('(event/@timestamp)[1]',  'datetime2')    AS event_time,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS duration_us,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS db_name
FROM
(
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions       s ON s.address = t.event_session_address
    WHERE s.name    = 'LongQueries'
      AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event') AS XEventData(event_data)
ORDER BY event_time DESC;

-- sys.fn_xe_file_target_read_file(path, metadata_file, initial_file_name, initial_offset)
-- Use wildcards to read all rolled files

SELECT
    event_data,
    file_name,
    file_offset
FROM sys.fn_xe_file_target_read_file(
    N'D:\XELogs\LongQueries*.xel',  -- path with wildcard
    NULL,                             -- metadata file (usually NULL)
    NULL,                             -- start file name (NULL = oldest)
    NULL                              -- start offset (NULL = beginning)
)
ORDER BY file_name, file_offset;     -- preserves chronological order within each file

DECLARE @last_file   NVARCHAR(260) = NULL;
DECLARE @last_offset BIGINT        = NULL;

-- On first poll, these are NULL (reads from beginning)
-- On subsequent polls, pass in values from previous run to read only new events

SELECT TOP 1
    @last_file   = file_name,
    @last_offset = file_offset
FROM sys.fn_xe_file_target_read_file(
    N'D:\XELogs\LongQueries*.xel', NULL, @last_file, @last_offset
)
ORDER BY file_name DESC, file_offset DESC;

-- Check ring buffer fullness
SELECT
    s.name                                                          AS session_name,
    t.target_name,
    CAST(t.target_data AS XML).value(
        '(RingBufferTarget/@eventCount)[1]', 'int')                AS event_count,
    CAST(t.target_data AS XML).value(
        '(RingBufferTarget/@memoryUsed)[1]', 'bigint')             AS memory_used_bytes
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions         s ON s.address = t.event_session_address
WHERE t.target_name = 'ring_buffer';

-- Writing to Azure Blob (2019+)
ADD TARGET package0.event_file
(
    SET filename = N'https://mystorageacct.blob.core.windows.net/xelogs/session.xel'
)

-- Find all events that contain "deadlock" in name or description
SELECT o.name, o.description, o.package_guid
FROM sys.dm_xe_objects o
WHERE o.object_type = 'event'
  AND (o.name LIKE '%deadlock%' OR o.description LIKE '%deadlock%');

-- Find all fields available on an event
SELECT c.name, c.type_name, c.description
FROM sys.dm_xe_object_columns c
JOIN sys.dm_xe_objects         o ON o.name = c.object_name
WHERE o.name = 'sql_statement_completed'
ORDER BY c.column_type;  -- 'data' = event field; 'action' = attached action

-- Decode integer values (e.g., recompile_cause)
SELECT map_value, map_key
FROM sys.dm_xe_map_values
WHERE name = 'statement_recompile_cause'
ORDER BY map_key;

-- Find system_health XEL files
SELECT
    path + '\system_health*.xel' AS file_path
FROM sys.dm_os_server_diagnostics_log_configurations;

-- Recent deadlocks from system_health ring buffer
SELECT TOP 10
    xdr.value('@timestamp', 'datetime2')   AS deadlock_time,
    xdr.query('.')                          AS deadlock_xml
FROM
(
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
    WHERE s.name = 'system_health'
      AND t.target_name = 'ring_buffer'
) AS d
CROSS APPLY target_data.nodes(
    '//RingBufferTarget/event[@name="xml_deadlock_report"]'
) AS XEventData(xdr)
ORDER BY deadlock_time DESC;

-- Read system_health from file (more history)
SELECT
    CAST(event_data AS XML).value(
        '(event/@timestamp)[1]', 'datetime2') AS event_time,
    CAST(event_data AS XML).value(
        '(event/@name)[1]', 'nvarchar(128)')  AS event_name,
    CAST(event_data AS XML)                    AS event_xml
FROM sys.fn_xe_file_target_read_file(
    (SELECT path + '\system_health*.xel'
     FROM sys.dm_os_server_diagnostics_log_configurations),
    NULL, NULL, NULL
)
WHERE CAST(event_data AS XML).value('(event/@name)[1]', 'nvarchar(128)')
      IN ('xml_deadlock_report', 'error_reported', 'wait_info')
ORDER BY event_time DESC;

SELECT
    s.name                AS session_name,
    s.event_session_id,
    s.pending_buffers,
    s.total_regular_buffers,
    s.total_large_buffers,
    CASE WHEN s.create_time IS NOT NULL THEN 'RUNNING' ELSE 'STOPPED' END AS state
FROM sys.dm_xe_sessions s;

SELECT
    s.name         AS session_name,
    e.name         AS event_name,
    a.name         AS action_name
FROM sys.dm_xe_sessions           s
JOIN sys.dm_xe_session_events     e ON e.event_session_address = s.address
JOIN sys.dm_xe_session_event_actions a ON a.event_session_address = e.event_session_address
                                       AND a.event_name = e.name
WHERE s.name = 'LongQueries'
ORDER BY e.name, a.name;

-- Persisted session definitions (CREATE EVENT SESSION without ON SERVER → persisted in master)
SELECT name, event_session_id, startup_state
FROM sys.server_event_sessions;

-- vs active (running) sessions
SELECT name FROM sys.dm_xe_sessions;