Extended Events and Query Store Conclusion

This module modernizes an older “Profiler / Trace Wizard” workflow into the current SQL Server troubleshooting model: Extended Events (XE) for targeted diagnostics, and Query Store for persistent performance history and regression analysis. Profiler-era traces were useful, but they introduced avoidable overhead and required you to "catch the problem live." In SQL Server 2025, the recommended approach is to keep Query Store collecting baselines while you use XE sessions as lightweight, purpose-built captures when you need deeper visibility.

Extended Events: the modern replacement for tracing

Extended Events is built around a small set of composable concepts:

• Event session: the container you create and start/stop.
• Events: what you capture (for example: sql_batch_completed, rpc_completed, error_reported).
• Actions: optional context attached to an event (for example: client app, database, session id, SQL text).
• Predicates: filters that decide what is captured (the WHERE clause in the session definition).
• Targets: where captured events go (for example: event_file for durable storage, or ring_buffer for short live troubleshooting).

In practice, an XE session is defined with CREATE EVENT SESSION, one or more ADD EVENT blocks, one or more ADD TARGET blocks, and optional session settings in the WITH (...) clause (for example: dispatch latency, retention behavior, memory partitioning, startup state, and causality tracking).

Migration tip: map SQL Trace event classes to XE equivalents

If you are upgrading older course material (or migrating a legacy trace workflow), Microsoft publishes a mapping that helps you translate SQL Trace “event classes” to their Extended Events equivalents. For example, Profiler’s “RPC:Completed” maps to the XE event sqlserver.rpc_completed, and “SQL:BatchCompleted” maps to sqlserver.sql_batch_completed. Use that mapping as your starting point, then refine your session with predicates and only the actions you actually need.

Query Store: the “always-on” performance flight recorder

Query Store complements XE by continuously collecting query text, plans, and runtime statistics inside the database, enabling trend analysis and fast regression detection. Instead of replaying a captured trace workload, administrators use Query Store’s built-in reports (such as “Regressed Queries” and “Top Resource Consuming Queries”) to identify which plan change caused a slowdown, then stabilize performance using plan forcing or other Query Store-driven tuning options.

What you should be able to do after this module

1. Explain why SQL Trace / Profiler workflows are legacy and why XE is the supported diagnostics framework.
2. Create an XE session by selecting events, attaching actions, filtering with predicates, and writing to targets.
3. Use Microsoft’s “Trace → XE equivalents” mapping to migrate legacy trace designs.
4. Run and analyze XE sessions using targets appropriate for production (typically event_file) and short investigations (often ring_buffer).
5. Use Query Store to identify regressed queries, compare time windows, and stabilize performance using Query Store features.

Glossary terms

Extended Events Exercise