14

If you add "too many" actions to an event in an event session, you will receive this error:

Msg 25639, Level 16, State 23, Line 1 The event, "[event name]", exceeds the number of allowable bound actions.

How many actions are allowed? Does it vary by event?

The answer, based on experimentation, appears to be 27 for sqlserver.rpc_completed. But I haven't found that number on any Microsoft documentation. And it seems to vary by event, as I was able to get 30 for sqlserver.sql_batch_completed.

Example code which fails:

CREATE EVENT SESSION [Test] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(
        package0.callstack,
        package0.collect_cpu_cycle_time,
        package0.collect_current_thread_id,
        package0.collect_system_time,
        package0.event_sequence,
        package0.last_error,
        package0.process_id,
        sqlos.cpu_id,
        sqlos.numa_node_id,
        sqlos.scheduler_address,
        sqlos.scheduler_id,
        sqlos.system_thread_id,
        sqlos.task_address,
        sqlos.task_elapsed_quantum,
        sqlos.task_resource_group_id,
        sqlos.task_resource_pool_id,
        sqlos.task_time,
        sqlos.worker_address,
        sqlserver.client_app_name,
        sqlserver.client_connection_id,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.context_info,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.is_system,
        sqlserver.nt_username,
        sqlserver.plan_handle))
GO
DROP EVENT SESSION [Test] ON SERVER
GO

Example code which succeeds (the same except excluding the last item):

CREATE EVENT SESSION [Test] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(
        package0.callstack,
        package0.collect_cpu_cycle_time,
        package0.collect_current_thread_id,
        package0.collect_system_time,
        package0.event_sequence,
        package0.last_error,
        package0.process_id,
        sqlos.cpu_id,
        sqlos.numa_node_id,
        sqlos.scheduler_address,
        sqlos.scheduler_id,
        sqlos.system_thread_id,
        sqlos.task_address,
        sqlos.task_elapsed_quantum,
        sqlos.task_resource_group_id,
        sqlos.task_resource_pool_id,
        sqlos.task_time,
        sqlos.worker_address,
        sqlserver.client_app_name,
        sqlserver.client_connection_id,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.context_info,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.is_system,
        sqlserver.nt_username))
GO
DROP EVENT SESSION [Test] ON SERVER
GO

(I tried a couple of different actions and it doesn't seem to relate to which actions are included-- but maybe it's based on a total character count of action names?)

Full list of actions I was working with:

package0.callstack,
package0.collect_cpu_cycle_time,
package0.collect_current_thread_id,
package0.collect_system_time,
package0.event_sequence,
package0.last_error,
package0.process_id,
sqlos.cpu_id,
sqlos.numa_node_id,
sqlos.scheduler_address,
sqlos.scheduler_id,
sqlos.system_thread_id,
sqlos.task_address,
sqlos.task_elapsed_quantum,
sqlos.task_resource_group_id,
sqlos.task_resource_pool_id,
sqlos.task_time,
sqlos.worker_address,
sqlserver.client_app_name,
sqlserver.client_connection_id,
sqlserver.client_hostname,
sqlserver.client_pid,
sqlserver.context_info,
sqlserver.database_id,
sqlserver.database_name,
sqlserver.is_system,
sqlserver.nt_username,
sqlserver.plan_handle,
sqlserver.query_hash,
sqlserver.query_hash_signed,
sqlserver.query_plan_hash,
sqlserver.query_plan_hash_signed,
sqlserver.request_id,
sqlserver.server_instance_name,
sqlserver.server_principal_name,
sqlserver.server_principal_sid,
sqlserver.session_id,
sqlserver.session_nt_username,
sqlserver.session_resource_group_id,
sqlserver.session_resource_pool_id,
sqlserver.session_server_principal_name

@@VERSION Output:

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) 
    Oct 28 2016 18:17:30 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
9
+150

How many actions are allowed? Does it vary by event?

I did some research and yes, there is a limit to the number of actions and events that can be added to an extended event definition. It's not a "hard" value but based on many different inputs, thus one definition that doesn't work could work with just the removal of a single event or a single action in a single event.

And it seems to vary by event, as I was able to get 30 for sqlserver.sql_batch_completed.

You've already stumbled upon the myriad of configurations that are possible, so you know it's not entirely based on the number of actions. It's also not specific to each event, but a combination of the values.

What can you do?

The first item is that variable length data is the largest issue you're going to face. How do you know what's variable length and what isn't? If you look in the XE catalog sys.dm_xe_objects specifically at some actions, you'll see there is type_name and type_size columns which can be useful to see if you're adding a bunch of variable sized data points (size of 0 in the screenshot below).

enter image description here

Now, you're probably thinking - ok that's great but I don't know the magical limit so it really isn't helpful. Well, it is and it isn't. If you specifically look at it from a numbers point of view then yes it isn't very helpful... however this is a terrible way of looking at it. It should be looked at as, "Am I collecting only the data I need?" and in most cases you'll never run into an issue with this error.

If we take the definition in the question that doesn't work, some of the information collected seems as though it really isn't needed. Do you really need callstack, current thread id, cpu cycle time, worker address, and scheduler address? Callstack is variable, the rest are fixed, so just eliminating the callstack you could fit in more columns if needed. I'm not saying you need any more but you could.

The whole point is to limit the definition to be as small as needed. Collecting everything is going to either result in errors (as you've had here), system slowness, too much data for analyzing, or even system halting. Just because you can doesn't mean you should. There is nothing stating that these limits will or won't change between major or minor versions, so keeping the true minimum need is the best prevention. Please don't just check every box (gui) or add every action possible.

| improve this answer | |
  • I ran into similar issues when adding too many events to a single session when testing a QueryableXEventData tool I was developing. Specifying only those events, actions, and fields actually needed is good advice. – Dan Guzman Mar 24 '18 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.