In the ever-evolving world of database administration, visibility is power. Understanding what is happening inside SQL Server is crucial for diagnosing problems, optimizing performance, and ensuring stability. That is where Extended Events come in. Extended Events is a lightweight and highly customizable replacement of SQL Trace and Profiler which offer deep insights into SQL Server’s internal behavior without imposing significant overhead. These make Extended Events a perfect choice for troubleshooting deadlocks, monitoring long-running queries, analyzing I/O bottlenecks, trace, filter, and respond to specific database events with precision.
In this guide, we will walk through how to set up Extended Events in SQL Server Management Studio (SSMS), explain the key configuration options, and explore real-world use cases that make Extended Events an essential tool in every DBA’s toolbox.
Figure-1: Extended Events
Extended Events
Extended Events is a lightweight performance monitoring tool in SQL Server that helps you gather detailed insights about system behavior. It allows you to track and analyze internal operations of the database engine, making it easier to identify and troubleshoot performance issues. With Extended Events, you can target specific events without adding much overhead, ensuring efficient and focused monitoring.
Configure Extended Events
From Object explorer, Management -> Extended Events -> right click on Sessions and click on New Session (Figure-2).
Figure-2: Object Explorer
Give a meaningful name of the session. There are couple of templates. You can choose based on your requirements (Figure-3).
Templates
- Query Batch Sampling: It captures queries and procedure calls for 20% of all active sessions.
- Query Batch Tracking: It will capture all queries and procedures from all sessions.
- Query Detail Sampling: It will collect every statement in queries and procedures for 20% of all active sessions.
- Query Detail Tracking: In addition to Query Batch Tracking, it collects every single statement. Thus it amasses huge data.
- Query Wait Statistic: It captures wait statistics for each statement of every query and procedure for 20% of all active sessions.
Figure-3: Event templates
In the Event library area, search for sql/rpc and add necessary events in "Selected events" area (figure-4). Description of some useful events are given below:
- rpc_completed - The stored procedure was executed using the Remote Procedure Call (RPC) mechanism through an OLEDB command. If a database application executes a stored procedure using the T-SQL EXECUTE statement, then that stored procedure is resolved as a SQL batch.
- sp_statement_completed - A SQL statement completion event within a stored procedure.
- sql_batch_completed - A T-SQL batch is a set of SQL queries that are submitted together to SQL Server and usually terminated by GO command.
- sql_statement_completed - A T-SQL statement completion event.
- error_reported - It occurs when an error is reported.
Figure-4: Event library
Clicking on the Configure button (Figure-4) will take you to new window Global Fields (Actions) (Figure-5). Some common Global Fields (Actions) are:
- plan_handle
- query_hash
- query_plan_hash
- database_name
- client_app_name
- transaction_id
- session_id
Figure-5: Global fields
You can apply filter to capture your desired output list (Figure-6). Some common filters are:
- sqlserver.username = <some value>
- duration >= 100
- physical_reads >= 3
Figure-6: Filter predicate
The standard event fields are attached with corresponding event (Figure-7). Some common fields are:
- batch text - The SQL text from the sql_batch_completed event.
- cpu time - The CPU cost of an event in microseconds (mc). For example, CPU = 100 for a SELECT statement indicates that the statement took 100mc to execute.
- logical reads - The number of logical reads performed for an event. For example, logical_reads = 800 for a SELECT statement indicates that the statement required a total of 800 page reads.
- physical reads - The number of physical reads performed for an event. This can differ from the logical_reads value because of access to the disk subsystem.
- writes - The number of logical writes performed for an event.
- duration - The execution time of an event in ms.
Figure-7: Event fields
The next step is data storage (Figure-8). You can either save the captured data in file (no data loss) or in ring buffer i.e. so data will be over written periodically. Finally, click the Ok button and finish the session.
Figure-8: Data storage
Start The Data Capture
Now right click on the newly created extended event and click start session. Then again right click on the extended event and click on the watch live data. You will observe the live data (Figure-9). You can also browse the saved report in the disk. You can also search/sort/group by within a column on the collected data.
Figure-9: Live data
Recommendation
- Extended events has negative impacts on performance. Think about Query Store which can provide a lot of
information with less impact.
- Set the max file size appropriately. The default value (1GB) is not sufficient when you collect a lot of data.
- Be cautious with debug events. Without direct guidance from Microsoft, do not use them. As those are subject to change and are meant for Microsoft internal use only.
- Avoid use of No_Event_Loss as it will place additional load on the SQL Server.
Final Words
Extended Events offers a powerful, flexible, and lightweight way to monitor SQL Server performance and troubleshoot issues effectively. Whether you're diagnosing a slow query, tracking deadlocks, or analyzing resource usage, Extended Events gives you deep visibility into the inner workings of the SQL Server engine. With careful setup and targeted sessions, DBAs can capture just the right data without affecting performance. As you get familiar with its capabilities, Extended Events can become an essential tool in your performance tuning and diagnostics toolkit.
References
- Quickstart: Extended Events
- SQL Server 2017 Query Performance Tuning Troubleshoot and Optimize Query Performance, Fifth Edition By Grant Fritchey
Going Further
If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too!
Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.
Let’s make your SQL Server to take your business Challenge!
For any queries, mail to mamehedi.hasan[at]gmail.com.