Using Extended Events for SQL Server Performance Troubleshooting (with Real-Time Examples)

SQL Server Extended Events (XE) is the modern replacement for SQL Trace and Profiler. It provides a lightweight, highly customizable way to capture diagnostic and performance data, making it one of the most powerful tools for DBAs to troubleshoot production issues efficiently.

In this guide, we’ll cover:

• What Extended Events are
• How to create and manage XE sessions
• Real-time troubleshooting examples (high CPU, blocking, deadlocks, wait stats)
• Querying XE data for insights
• Best practices

What Are Extended Events?

Extended Events is a built-in event-handling framework in SQL Server. It allows you to monitor performance and troubleshoot issues with minimal overhead compared to SQL Profiler.

Key advantages include:

• Lightweight and efficient
• Powerful filtering capabilities
• Ability to capture detailed data (queries, waits, execution plans, deadlocks, memory usage)
• Integration with SSMS “Live Data” viewer

Step 1: Creating a Basic Extended Events Session

For example, let’s capture long-running queries (greater than 1 second):

CREATE EVENT SESSION [LongRunningQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.database_id,
        sqlserver.session_id,
        sqlserver.client_app_name,
        sqlserver.client_hostname
    )
    WHERE (duration > 1000000)  -- 1 second in microseconds
)
ADD TARGET package0.event_file (SET filename = 'C:\XE\LongRunningQueries.xel', max_file_size = 50)
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, TRACK_CAUSALITY = ON);

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

This session captures all completed SQL statements that run longer than 1 second and saves them into an .xel file.

Step 2: Querying Extended Events Data

After running the session for some time, you can analyze the captured events:

SELECT
    event_data.value('(event/@name)[1]', 'varchar(50)') AS EventName,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint')/1000 AS Duration_ms,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS SQLText,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(100)') AS AppName,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(100)') AS HostName,
    event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS SessionID
FROM (
    SELECT CAST(event_data AS XML) event_data
    FROM sys.fn_xe_file_target_read_file('C:\XE\LongRunningQueries*.xel', NULL, NULL, NULL)
) AS X;

Real-Time Troubleshooting Examples

1. High CPU Query Investigation

Identify queries consuming excessive CPU:

CREATE EVENT SESSION [HighCPUQueries] ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.sql_text, sqlserver.session_id, sqlserver.client_hostname)
    WHERE (cpu_time > 500000)  -- > 500 ms CPU
)
ADD TARGET package0.event_file (SET filename='C:\XE\HighCPUQueries.xel');
ALTER EVENT SESSION [HighCPUQueries] ON SERVER STATE=START;

Use this session when you experience CPU spikes to find the top offenders.

2. Blocking Sessions

Capture blocking chains in real-time:

CREATE EVENT SESSION [BlockingSessions] ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file (SET filename='C:\XE\Blocking.xel');
ALTER EVENT SESSION [BlockingSessions] ON SERVER STATE=START;

This records XML reports of blocking sessions, helping you identify who is blocking whom.

3. Deadlocks

Deadlocks are automatically written to the Error Log, but XE provides richer context:

CREATE EVENT SESSION [Deadlocks] ON SERVER
ADD EVENT sqlserver.lock_deadlock
ADD EVENT sqlserver.lock_deadlock_chain
ADD TARGET package0.event_file (SET filename='C:\XE\Deadlocks.xel');
ALTER EVENT SESSION [Deadlocks] ON SERVER STATE=START;

Open the .xel file in SSMS → “View Target Data” → Deadlock Graphs.

4. Wait Statistics

Troubleshoot performance bottlenecks by capturing wait events:

CREATE EVENT SESSION [Waits] ON SERVER
ADD EVENT sqlserver.wait_info (
    ACTION (sqlserver.session_id, sqlserver.sql_text)
    WHERE (duration > 1000)  -- waits longer than 1 ms
)
ADD TARGET package0.event_file (SET filename='C:\XE\Waits.xel');
ALTER EVENT SESSION [Waits] ON SERVER STATE=START;

Useful for identifying if slowness is due to PAGEIOLATCH, CXPACKET, etc.

Step 3: Monitoring Live Data

• In SSMS, go to Management → Extended Events → Sessions
• Right-click your session → Watch Live Data
• Filter, group, and sort data directly in the GUI to spot issues in real time.

Step 4: Stopping and Dropping a Session

Always stop sessions after troubleshooting to avoid unnecessary overhead:

ALTER EVENT SESSION [LongRunningQueries] ON SERVER STATE = STOP;
DROP EVENT SESSION [LongRunningQueries] ON SERVER;

Best Practices

✅ Use filters (duration, cpu_time, database_id) to reduce overhead
✅ Store output in .xel files for long-running sessions
✅ Avoid leaving sessions running indefinitely in production
✅ Combine XE with Query Store for comprehensive troubleshooting

Extended Events Quick Reference Checklist

• Create a session
  • CREATE EVENT SESSION with events, actions, filters, and target
• Start the session
  ALTER EVENT SESSION [YourSession] ON SERVER STATE = START;
• Monitor live
  • SSMS → Management → Extended Events → Sessions → Watch Live Data
• Query results
  SELECT CAST(event_data AS XML) AS EventData

  FROM sys.fn_xe_file_target_read_file(‘C:\XE\*.xel’, NULL, NULL, NULL);
• Stop the session
  ALTER EVENT SESSION [YourSession] ON SERVER STATE = STOP;
• Drop the session (if no longer needed)
  DROP EVENT SESSION [YourSession] ON SERVER;

Next Steps

Try creating a custom XE session tailored to your workload and integrate it into your standard troubleshooting toolkit. Start with simple sessions (e.g., deadlocks, high CPU) and expand as you gain confidence.

✅ Conclusion

Extended Events provide DBAs with fine-grained control over SQL Server monitoring and diagnostics while keeping performance overhead low. Whether you’re troubleshooting high CPU queries, blocking, deadlocks, or waits, Extended Events give you the visibility needed to resolve issues quickly and effectively.