Lesson 1

Extended Events (often abbreviated as XEvents) is SQL Server’s modern, lightweight monitoring and diagnostics framework. It was introduced in SQL Server 2008 and reached practical feature parity with SQL Trace/Profiler workflows by the SQL Server 2012 era. Because SQL Server Profiler depends on SQL Trace technology (deprecated since SQL Server 2012), Extended Events is the recommended baseline for troubleshooting performance issues, investigating blocking and deadlocks, and capturing query and server behavior with minimal overhead.

Conceptually, an Extended Events session is a configurable “instrumentation pipeline”: you select the events you care about, add actions (extra payload such as SQL text or session context), filter aggressively to reduce noise, and write results to a target (commonly an event_file) for later analysis.

• Lower overhead by design: XEvents can capture high-value diagnostics without the heavy impact historically associated with broad trace captures.
• Fine-grained control: you can precisely select events, add targeted payload, and apply filters (predicates) so you collect only what you need.
• Operationally safer: sessions can run continuously with controlled scope, and the event_file target supports retention policies.
• Modern tooling and longevity: SSMS provides a UI for sessions and file viewing, and the platform aligns with current SQL Server engineering direction.

In short: if you are diagnosing waits, blocking, deadlocks, long-running queries, login storms, or intermittent errors, Extended Events is the default capture mechanism you should reach for first.

By the end of this module, you should be able to:

1. Explain the Extended Events model (events, actions, predicates, targets, sessions).
2. Create and start an Extended Events session using SSMS and/or T-SQL.
3. Capture targeted troubleshooting data (duration, CPU, logical reads, row counts, and error context).
4. Store and review diagnostic output using the event_file target and SQL Server functions.
5. Use built-in sessions (for example, system_health) as a first stop for common incident response.
6. Apply Extended Events to concurrency issues (blocking chains, deadlocks, lock timeouts) with minimal disruption.
7. Recognize when to pair XEvents with modern features such as Query Store for end-to-end performance analysis.

Events

An event represents something that occurred inside SQL Server (for example, a statement completing, a lock being acquired, a deadlock graph being produced, or an error being raised).

Actions

An action adds context to an event (for example, the database name, the client host name, the session id, or the SQL text). Actions increase observability, but you should include only what you will actually use, to keep capture lightweight.

Predicates (Filters)

A predicate filters events at collection time. This is one of the biggest differences from “capture everything” tracing habits: filtering is how you keep overhead low and output useful.

Targets

A target determines where the captured data goes. In production, the most common durable target is event_file, which writes to disk and supports controlled retention. The ring_buffer target is useful for quick, short-lived troubleshooting but is not durable across restarts.

The following example illustrates a typical workflow: create a session, filter to reduce noise, write to an event file, start it, and then review the capture. Treat this as a conceptual template—you will refine the event list and filters based on the scenario you are troubleshooting.

-- Example: capture long-running statement completions to an event_file
CREATE EVENT SESSION [LongRunningStatements] 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
  )
  WHERE (duration >= 5000000) -- duration in microseconds (example: 5 seconds)
)
ADD TARGET package0.event_file
(
  SET filename = N'/var/opt/mssql/xevents/LongRunningStatements.xel',
      max_file_size = (50),
      max_rollover_files = (4)
);
GO

ALTER EVENT SESSION [LongRunningStatements] ON SERVER STATE = START;
GO

To read an .xel file capture using T-SQL, you can use sys.fn_xe_file_target_read_file and project the data you need:

SELECT
  DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), SYSDATETIME()), xed.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS event_time_local,
  xed.event_data.value('(event/action[@name="database_name"]/value)[1]', 'sysname') AS database_name,
  xed.event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS session_id,
  xed.event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS duration_us,
  xed.event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text
FROM
(
  SELECT CAST(event_data AS xml) AS event_data
  FROM sys.fn_xe_file_target_read_file
  (
    N'/var/opt/mssql/xevents/LongRunningStatements*.xel',
    NULL, NULL, NULL
  )
) AS xed
ORDER BY event_time_local DESC;

In the next lesson, you will move from concepts to execution: building sessions in SSMS, selecting events/actions responsibly, filtering correctly, and choosing targets that fit real operational constraints.

• Start narrow: define the question you are trying to answer, then capture only what supports that question.
• Prefer durable targets for incidents: event_file supports post-mortem analysis and controlled retention.
• Know your baseline tools: combine XEvents with DMVs (waits, sessions, locks) and Query Store for workload history and regression analysis.
• Document and reuse: keep a small library of vetted sessions (blocking, deadlocks, long-running queries, login failures) you can enable quickly.