Did you know that there is a limit to the number of actions you can add to a single event in Extended Events? While I was playing around with Trace Flag 9708 for my previous blog post, one of the things I wanted to try to see was whether it would be easy to determine the impact of adding too many actions to a single event. I picked a single event, in this case wait_info, and I checked all of the actions for the event in the UI, and tried to create the event session. I was surprised when I got a validation error back so I clicked on the details link and got the following:
To be totally clear, this is NOT something I would recommend or that I would even consider to be realistic within the framework of how to use Extended Events at all. The entire purpose of Extended Events was to reduce the overhead of diagnostics data collection by making the events as lightweight as possible, but still providing the flexibility to add additional context information to the event data payloads through the implementation of actions. Generally speaking, I wouldn’t add actions to an event just for the sake of adding actions like this in real life, I was just trying to play around with things on my test machine.
I decided to script out the definition instead of just creating it through the UI, and it will certainly generate the CREATE/ALTER EVENT SESSION DDL for all of the actions available and selected in the UI, but the exception gets raised when you attempt to execute the script. So, I started commenting out one action at a time until it was able to create the event session without the exception. Then I used the following script to determine how many of the actions were able to be added:
SELECT ses.name as session_name,
sese.name AS event_name,
sesa.package + '.' + sesa.name AS action_name
FROM sys.server_event_sessions AS ses
INNER JOIN sys.server_event_session_events AS sese
ON ses.event_session_id = sese.event_session_id
INNER JOIN sys.server_event_session_actions AS sesa
ON sese.event_session_id = sesa.event_session_id
AND sese.event_id = sesa.event_id
WHERE ses.name = 'CrazyEvents'
AND sese.name = 'wait_info'
On SQL Server 2022 I was able to add 32 actions to a single event without any exception being raised. What I found interesting though was that on SQL Server 2019, I could only add 31 actions to a single event. I’m not sure why there is one less on 2019, at least as of build15.0.4198.2, which is a couple of CU’s behind at this point. Not sure why there is a different limitation on 2019, but to be honest, I don’t see it as much of a limitation to practical usage of Extended Events. I’ve never had to add more than a handful of actions to events in real world use cases where I would use Extended Events for data collection.