SQL Server

SQL Server Extended Events Monitoring

Expert guide on sql server extended events monitoring with practical examples and best practices for database administrators.

Thu Jul 31 2025 08:09:00 GMT+0000 (Coordinated Universal Time) Jamaurice Holt 5 min read
sql-server database optimization

📊 SQL Server Extended Events: The Complete Monitoring Guide

Extended Events (XEvents) is SQL Server's modern, lightweight, and highly scalable event-handling system. It replaces SQL Trace and SQL Profiler with a more efficient, less intrusive monitoring framework. This comprehensive guide covers everything you need to master Extended Events for production monitoring.

🔧 Understanding Extended Events Architecture

Core Components

ComponentDescription
EventsPoints of interest (query completion, errors, waits)
ActionsAdditional data to collect when event fires
PredicatesFilters to limit which events are captured
TargetsDestinations for event data (files, ring buffer)
SessionsContainer that ties events, actions, and targets together

Extended Events vs SQL Trace

FeatureSQL TraceExtended Events
Overhead10-30%1-3%
FilteringColumn-basedPredicate expressions
ScalabilityLimitedHighly scalable
Async processingNoYes
Memory managementFixed buffersDynamic allocation
Future supportDeprecatedActive development

🚀 Creating Extended Events Sessions

Basic Query Monitoring Session

-- Monitor slow queries (> 5 seconds)
CREATE EVENT SESSION [SlowQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.session_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE
    (
        duration >= 5000000  -- 5 seconds in microseconds
    )
),
ADD EVENT sqlserver.rpc_completed
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.session_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE
    (
        duration >= 5000000
    )
)
ADD TARGET package0.event_file
(
    SET filename = N'C:\SQLLogs\SlowQueries.xel',
        max_file_size = 100,      -- MB
        max_rollover_files = 10
)
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    STARTUP_STATE = ON
);

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

Error and Exception Monitoring

-- Capture all errors and exceptions
CREATE EVENT SESSION [ErrorMonitoring] ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.session_id,
        sqlserver.sql_text,
        sqlserver.tsql_stack,
        sqlserver.username
    )
    WHERE
    (
        severity >= 11  -- Errors only, not informational
    )
),
ADD EVENT sqlserver.exception_ring_buffer_recorded
(
    ACTION
    (
        sqlserver.session_id,
        sqlserver.sql_text
    )
)
ADD TARGET package0.event_file
(
    SET filename = N'C:\SQLLogs\Errors.xel',
        max_file_size = 50,
        max_rollover_files = 20
),
ADD TARGET package0.ring_buffer
(
    SET max_memory = 4096
)
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE = ON
);

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

Wait Statistics Monitoring

-- Capture wait events for specific wait types
CREATE EVENT SESSION [WaitAnalysis] ON SERVER
ADD EVENT sqlos.wait_completed
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.database_name,
        sqlserver.plan_handle,
        sqlserver.query_hash,
        sqlserver.session_id,
        sqlserver.sql_text
    )
    WHERE
    (
        -- Focus on significant waits
        duration > 1000  -- > 1ms
        AND
        (
            wait_type = 'LCK_M_X' OR
            wait_type = 'LCK_M_U' OR
            wait_type = 'LCK_M_S' OR
            wait_type = 'PAGEIOLATCH_SH' OR
            wait_type = 'PAGEIOLATCH_EX' OR
            wait_type = 'WRITELOG' OR
            wait_type = 'ASYNC_NETWORK_IO' OR
            wait_type = 'CXPACKET'
        )
    )
)
ADD TARGET package0.event_file
(
    SET filename = N'C:\SQLLogs\Waits.xel',
        max_file_size = 100,
        max_rollover_files = 5
)
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    STARTUP_STATE = OFF
);

💡 Common Monitoring Scenarios

Deadlock Monitoring

-- Comprehensive deadlock capture
CREATE EVENT SESSION [DeadlockMonitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name
    )
),
ADD EVENT sqlserver.lock_deadlock
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.database_name,
        sqlserver.session_id,
        sqlserver.sql_text
    )
),
ADD EVENT sqlserver.lock_deadlock_chain
(
    ACTION
    (
        sqlserver.session_id,
        sqlserver.sql_text
    )
)
ADD TARGET package0.event_file
(
    SET filename = N'C:\SQLLogs\Deadlocks.xel',
        max_file_size = 50,
        max_rollover_files = 10
),
ADD TARGET package0.ring_buffer
(
    SET max_memory = 4096
)
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = NO_EVENT_LOSS,  -- Don't lose deadlock info
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE = ON
);

Login Auditing

-- Track all login attempts
CREATE EVENT SESSION [LoginAudit] ON SERVER
ADD EVENT sqlserver.login
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.server_principal_name
    )
),
ADD EVENT sqlserver.logout
(
    ACTION
    (
        sqlserver.session_id
    )
),
ADD EVENT sqlserver.login_failed
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname
    )
)
ADD TARGET package0.event_file
(
    SET filename = N'C:\SQLLogs\LoginAudit.xel',
        max_file_size = 100,
        max_rollover_files = 30  -- 30 days retention
)
WITH
(
    MAX_MEMORY = 2048 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    STARTUP_STATE = ON
);

Query Plan Changes

-- Monitor query plan recompilations
CREATE EVENT SESSION [PlanChanges] ON SERVER
ADD EVENT sqlserver.sql_statement_recompile
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.database_name,
        sqlserver.plan_handle,
        sqlserver.query_hash,
        sqlserver.sql_text
    )
),
ADD EVENT sqlserver.query_post_compilation_showplan
(
    ACTION
    (
        sqlserver.database_name,
        sqlserver.query_hash,
        sqlserver.sql_text
    )
    WHERE
    (
        duration > 1000000  -- Compilations > 1 second
    )
)
ADD TARGET package0.event_file
(
    SET filename = N'C:\SQLLogs\PlanChanges.xel',
        max_file_size = 100
)
WITH
(
    MAX_MEMORY = 4096 KB,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    STARTUP_STATE = ON
);

📊 Reading Extended Events Data

Query Ring Buffer Target

-- Read from ring buffer
SELECT
    event_data.value('(event/@name)[1]', 'nvarchar(100)') AS EventName,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS EventTime,
    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 LogicalReads,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQLText,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS DatabaseName,
    event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(128)') AS UserName
FROM
(
    SELECT CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    INNER JOIN sys.dm_xe_sessions s ON st.event_session_address = s.address
    WHERE s.name = 'SlowQueries'
      AND st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event') AS XEventData(event_data)
ORDER BY EventTime DESC;

Query File Target

-- Read from event file
SELECT
    event_data.value('(event/@name)[1]', 'nvarchar(100)') AS EventName,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS EventTime,
    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="physical_reads"]/value)[1]', 'bigint') AS PhysicalReads,
    event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS LogicalReads,
    event_data.value('(event/data[@name="writes"]/value)[1]', 'bigint') AS Writes,
    event_data.value('(event/data[@name="row_count"]/value)[1]', 'bigint') AS RowCount,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQLText,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS DatabaseName,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(128)') AS ClientHost
FROM
(
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\SQLLogs\SlowQueries*.xel', NULL, NULL, NULL)
) AS Events
ORDER BY EventTime DESC;

Aggregated Analysis

-- Top 10 slowest queries by total duration
WITH EventData AS (
    SELECT
        event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS duration,
        event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') AS cpu_time,
        event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads,
        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 database_name
    FROM
    (
        SELECT CAST(event_data AS XML) AS event_data
        FROM sys.fn_xe_file_target_read_file('C:\SQLLogs\SlowQueries*.xel', NULL, NULL, NULL)
    ) AS Events
)
SELECT TOP 10
    COUNT(*) AS ExecutionCount,
    SUM(duration) / 1000 AS TotalDuration_ms,
    AVG(duration) / 1000 AS AvgDuration_ms,
    MAX(duration) / 1000 AS MaxDuration_ms,
    SUM(logical_reads) AS TotalLogicalReads,
    database_name,
    LEFT(sql_text, 500) AS SQLPreview
FROM EventData
GROUP BY database_name, LEFT(sql_text, 500)
ORDER BY TotalDuration_ms DESC;

🔧 Advanced Session Configuration

Event Retention Modes

-- No event loss (may cause blocking if buffer full)
WITH (EVENT_RETENTION_MODE = NO_EVENT_LOSS)

-- Allow single event loss (recommended for most scenarios)
WITH (EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS)

-- Allow multiple event loss (lowest overhead)
WITH (EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS)

Memory Configuration

-- Configure memory based on event volume
WITH (
    MAX_MEMORY = 8192 KB,                    -- Buffer size
    MAX_EVENT_SIZE = 0 KB,                    -- Unlimited event size
    MEMORY_PARTITION_MODE = PER_CPU,          -- Memory per CPU
    TRACK_CAUSALITY = ON,                     -- Event correlation
    MAX_DISPATCH_LATENCY = 30 SECONDS         -- Write frequency
)

Predicate Optimization

-- Efficient predicate (evaluated first)
WHERE sqlserver.database_id = 5

-- Less efficient (requires string comparison)
WHERE sqlserver.database_name = N'AdventureWorks'

-- Combine predicates efficiently
WHERE
    duration >= 1000000  -- Check cheap predicates first
    AND sqlserver.database_id = 5
    AND sqlserver.session_id <> sqlserver.is_system

✅ Production Best Practices

Session Template for Production

-- Production-ready monitoring template
CREATE EVENT SESSION [ProductionMonitoring] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.plan_handle,
        sqlserver.query_hash,
        sqlserver.session_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE
    (
        duration >= 1000000  -- 1 second
        AND sqlserver.is_system = 0  -- Exclude system sessions
        AND sqlserver.database_id > 4  -- Exclude system databases
    )
),
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.database_name,
        sqlserver.session_id,
        sqlserver.sql_text
    )
    WHERE
    (
        severity >= 16  -- Significant errors only
    )
)
ADD TARGET package0.event_file
(
    SET filename = N'C:\SQLLogs\Production.xel',
        max_file_size = 100,
        max_rollover_files = 10
)
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 = PER_CPU,
    TRACK_CAUSALITY = OFF,  -- Reduces overhead
    STARTUP_STATE = ON
);

Monitoring Session Health

-- Check session status and statistics
SELECT
    s.name AS SessionName,
    s.create_time,
    s.total_regular_buffers,
    s.regular_buffer_size,
    s.total_large_buffers,
    s.large_buffer_size,
    s.dropped_event_count,
    s.dropped_buffer_count,
    s.blocked_event_fire_time,
    CASE WHEN s.blocked_event_fire_time > 0
         THEN 'Blocking occurred'
         ELSE 'OK' END AS Status
FROM sys.dm_xe_sessions s
WHERE s.name NOT LIKE 'system%';

-- Target statistics
SELECT
    s.name AS SessionName,
    t.target_name,
    t.execution_count,
    t.execution_duration_ms,
    CAST(t.target_data AS XML).value('(RingBufferTarget/@eventCount)[1]', 'int') AS EventCount
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address;

⚠️ Warning: If dropped_event_count is increasing, consider increasing MAX_MEMORY or changing EVENT_RETENTION_MODE.

📈 Automated Reporting

Daily Slow Query Report

-- Create stored procedure for daily report
CREATE PROCEDURE dbo.GenerateSlowQueryReport
    @StartDate DATETIME2 = NULL,
    @EndDate DATETIME2 = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF @StartDate IS NULL
        SET @StartDate = DATEADD(DAY, -1, CAST(GETDATE() AS DATE));
    IF @EndDate IS NULL
        SET @EndDate = CAST(GETDATE() AS DATE);

    ;WITH EventData AS (
        SELECT
            event_data.value('(event/@timestamp)[1]', 'datetime2') AS EventTime,
            event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS duration,
            event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') AS cpu_time,
            event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads,
            event_data.value('(event/action[@name="query_hash"]/value)[1]', 'varbinary(8)') AS query_hash,
            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 database_name
        FROM (
            SELECT CAST(event_data AS XML) AS event_data
            FROM sys.fn_xe_file_target_read_file('C:\SQLLogs\SlowQueries*.xel', NULL, NULL, NULL)
        ) AS Events
    )
    SELECT
        database_name AS DatabaseName,
        query_hash AS QueryHash,
        COUNT(*) AS ExecutionCount,
        SUM(duration) / 1000000 AS TotalDuration_sec,
        AVG(duration) / 1000 AS AvgDuration_ms,
        MAX(duration) / 1000 AS MaxDuration_ms,
        SUM(logical_reads) AS TotalLogicalReads,
        AVG(logical_reads) AS AvgLogicalReads,
        LEFT(MAX(sql_text), 1000) AS SQLPreview
    FROM EventData
    WHERE EventTime >= @StartDate
      AND EventTime < @EndDate
    GROUP BY database_name, query_hash
    ORDER BY TotalDuration_sec DESC;
END;
GO

Conclusion

Extended Events is the essential monitoring framework for modern SQL Server administration. Key takeaways:

  • Low overhead - 1-3% impact vs 10-30% for SQL Trace
  • Flexible filtering - Predicate expressions minimize data collection
  • Scalable storage - File and ring buffer targets for different needs
  • Rich data - Actions provide complete context for each event
  • Production ready - Designed for 24/7 monitoring

Master Extended Events to gain unprecedented visibility into your SQL Server performance.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in SQL Server, SQL optimization, and enterprise database solutions.

Related Articles

Need help with SQL Server optimization? Contact us for expert database consulting.

Continue Reading

Leadership

Leadership in Motion: Technical Leadership Lessons from Database Operations

How a decade of database administration shaped my approach to technical leadership — lessons on incident response, mentoring engineers,...

July 10, 2024 · Read article → Performance

Database Performance Tuning: Lessons from the Trenches

Real-world strategies for optimizing database performance in high-traffic production environments — query plan analysis, indexing, caching,...

August 22, 2024 · Read article → AI

AI Governance and Hardware: Why Sovereign Refusal Belongs in Silicon

Software guardrails can be prompted around. The real frontier in AI safety is anchoring refusal below the model — at the hardware level. A...

September 15, 2024 · Read article →