Filtering Extended Events Using Actions

Did you know, you can use Actions to Filter Extended Events? Well, you can. Filtering is one of the greatest ways in which Extended Events differentiates itself from other mechanisms of gathering information about the behavior of SQL Server. You can put Actions to work in your filtering. Best of all, the Actions don’t have to be collected in order to put them to work filtering your Extend Events.

Using Actions To Filter Extended Events

Actions, also called Global Fields, are additional bits of data that you can add to a given Event when you’re setting up an Extended Events Session. They are programmatic additions to the Event, as described here. Think of them sort of like triggers. In practice, adding an Action, database_name, to an Event, like the rpc_completed Event, would look like this:

CREATE EVENT SESSION [RemoteProcedureCalls]
ON SERVER
ADD EVENT sqlserver.rpc_completed
(ACTION
(
sqlserver.database_name
)
);

With this, you’ll get the database name returned as a part of the information returned from the Extended Event. That’s great. However, do you really want to see the database name, or do you just want to filter by it? If we wanted to use the Action to filter the Extended Event, but not return the values, we could just do this:

CREATE EVENT SESSION [RemoteProcedureCalls]
ON SERVER
    ADD EVENT sqlserver.rpc_completed
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks'));

Now, we’re using the database_name Action to filter the event, but we’re not bothering to capture that Action as a part of the event. Of course, you can also mix and match as needed.

Conclusion

Putting to work Actions to filter Extended Events is fairly straight forward as you can see. Actions in general are handy little things. However, understand that they are additional overhead to an event. Not so much that I would hesitate to use them, but enough that I would be judicious when using them. If you look through the list of all possible actions, there is attractive information there, but adding them all would make a given event unworkable. So, only use what you need, when you need it in your Events.

For even more on Extended Events, look through my blog.

---

If you want to learn more about using Extended Events, I’ll be teaching an in-person class in Mechelin, Belgium, at Data Minds connect this October, 2021. Please go here to read all about it and get registered.