Recently, I encountered an issue where an Extended Event caused significant memory problems on a SQL instance, which lead to a crash. Once we brought the instance back online, we decided to stop the Extended Event, as it was a recent change and the most logical culprit behind the memory issue. Upon further investigation, I discovered that the session was monitoring a specific stored procedure but was not optimally configured. These settings (or lack of) caused the Extended Event to consume excessive memory. Once the configuration was adjusted, the session was able to run smoothly again.
This experience made me reflect on how easy it is to create Extended Events using default settings, often without considering the tweaks needed to minimise their impact. It also made me wonder how many people still rely on SQL Profiler simply out of habit, despite its limitations.
This blog is written for beginners or people just wanting a refresh.
If you are still using profiler, don’t let the complexity of Extended Events stop you from trying. There are many reasons to use Extended Events over profiler.
- Uses fewer resources, less impact on server performance. Thus, it has better performance with less overhead
- Supports a wider range of events
- Offers advanced filtering capabilities
- Built-in templates for common scenarios
- Highly customisable with more flexibility
- Continuously updated and supported
It’s worth noting that even though Extended Events uses less resources than Profiler it can still cause performance issues if not configured correctly. Also don’t use it if you already have monitoring tools doing the same job.
Below is a step-by-step guide to creating a simple session (also known as an extended event) to monitor user login using SQL Server 2022.
In Management Studio go to Management – Extended Events – Sessions – right click – ‘New Session Wizard’
 |
 |
| This is the opening page, just click on ‘Next’ | Enter the name of the session
Here you can set the event to startup with the instance, however, don’t select this as ideally you want to review the settings before turning on the session. |
 |
 |
| Don’t use a template, instead you’ll be creating your own custom event. |
This provides a list of events you can monitor, search for ‘login’, once found click on it and then ‘>’ to add it. |
 |
 |
| This section allows you to select specific data you want to monitor. Feel free to select what you are interested in, above shows a few as an example. | Here you want to add one of the following filters:
Please note that this section is very important, here you want to add as many filters as possible. If this section is ignored you can run into potential performance issues. |
 |
 |
| Another important section, you need to make sure that you select an option otherwise you can run into memory issues.
The above example shows the default for saving the data to a file, which is ok for this purpose. |
Here you can review all the configuration you have set. |
 |
|
| Again, we are not going to start the session yet. |
Reviewing the settings (right click on the session and then ‘Properties’):
 |
|
| If you want the session to start up with the instance, then select this option. We won’t be using the causality tracking (how events are related to one another during query execution) option for this session. | You can review the events that you made under the Events page. |
 |
 |
| The Data Storage page will show us that the event data is being saved to a file. You will see that you can’t edit this, you will need to delete and re-create the setting should you need to make any changes. | Details of the advanced options will be in another blog, however it’s worth being aware of these. If set incorrectly these can cause performance issues. The defaults are ok for this session. |
Now you’ve reviewed the settings you can start the session (right click on the session and select ‘start session’). To view the data of the session you can expand the session and right click on the ‘Package0.event_file’ and select ‘View Target Data’. There are other ways to view the session via TSQL which will be covered in another blog.
Once the session has started then you should monitor the following:
- Resources on the SQL instance (stop the session should any resource issues occur)
- Any blocking
- How rapidly the event file grows (tweak accordingly)
- That’s your first Extended Event session completed and running.
That’s your first Extended Event session completed and running.
To wrap things up, here’s a few final tips that might come in handy:
Should you want to save your extended event you can script it out (right click on the session and select ‘Script Session as…’) and then save the TSQL it generates for you, example below:
Remember to only run it for as long as you need it. You can use the code below to stop it:
I hope this blog convinced you to use Extended Events and help you along the way – good luck!