Step-by-Step Guide to Using SQL Server Extended Events in SQL Server 2019

What Are SQL Server Extended Events?

SQL Server Extended Events (XEvents) is a lightweight event monitoring system used to track SQL Server activity. It is the recommended replacement for SQL Profiler and provides:
✅ Lower performance overhead compared to SQL Profiler.
✅ More powerful event filtering and logging.
✅ Ability to capture detailed query execution data.
✅ Integration with SQL Server Management Studio (SSMS) for analysis

Step 1: Open Extended Events in SQL Server 2019
1. Launch SSMS (SQL Server Management Studio)
Open SSMS and connect to your SQL Server instance. Expand Management → Extended Events.
2. Create a New Extended Events Session
Right-click Sessions → New Session Wizard. Click Next to begin the session creation process.

Step 2: Configure the Extended Events Session
1. Name the Session
Enter a name (e.g., Query_Performance_Tracking). Click Next.
2. Choose an Event Capture Method
Choose "Do not use a template" for full customization. Click Next.

Step 3: Select Events to Capture
You can capture different types of events depending on your needs.
1. Capture Slow Queries
Click Events → Add Event.

Step 4: Apply Filters (Optional, Recommended for Performance)
To reduce data collection overhead, filter events: Click Configure next to an event. Apply Filters

Step 5: Configure Data Storage (Where to Save the Event Data?)
Choose an output method:
Ring Buffer (In-memory, temporary storage). Event File (Recommended) – Saves to disk for later analysis. Set file size limits to avoid excessive disk usage. Click Next.

Step 6: Start and Monitor the Session
Click Finish to create and start the event session. Right-click your session → Click Start Session.

Step 7: Analyze Captured Data
Once events are captured, analyze them using the Live Data View:
1. View Event Details
Expand Extended Events → Sessions. Right-click your session → Watch Live Data. Look for long-running queries and blocking transactions.
2. Save and Export Data
Right-click on the event data → Save As → Choose a file format for analysis.
3. Identify Performance Bottlenecks
Run queries on the Extended Events data to extracts captured event details from the .xel file.
Step 8: Stop and Delete Extended Events Sessions (When Done)
To free resources, stop or delete unused sessions:
1. Stop the Session
ALTER EVENT SESSION Query_Performance_Tracking ON SERVER STATE=STOP;
Or, right-click on the session and click Stop Session.
2. Delete the Session
DROP EVENT SESSION Query_Performance_Tracking ON SERVER;
Or, right-click on the session and click Delete.