SQL Server Extended Events - collecting SP Object_name

Question

I'm going round in circles trying to work out how to collect the object_name in extended events, I can use it to filter like in the session definition below

CREATE EVENT SESSION [xetest] ON SERVER 
ADD EVENT sqlserver.module_start(
  ACTION(
    sqlserver.client_app_name,
    sqlserver.client_hostname,
    sqlserver.database_name /*, sqlserver.object_name*/
  )
  WHERE (
    [package0].[divides_by_uint64]([sqlserver].[session_id], (10))
    AND object_name = N'Price_GetGenericOwner'
  )
)
ADD TARGET package0.ring_buffer(
  SET
    max_events_limit = (10000),
    max_memory = (4096)
)
WITH (
  MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF
)
GO

But when I try and collect the data as per this second example I get an error message, my intention is to collect all the SP names and analyse the data but this is ahrd if I can't collect it, can anybody shed some light on what I am doing wrong ?

CREATE EVENT SESSION [xetest] ON SERVER 
ADD EVENT sqlserver.module_start(
  ACTION(
    sqlserver.client_app_name,
    sqlserver.client_hostname,
    sqlserver.database_name,
    object_name
  )
  WHERE (
    [package0].[divides_by_uint64]([sqlserver].[session_id], (10))
    AND object_name = N'Price_GetGenericOwner'
  )
)
ADD TARGET package0.ring_buffer(
  SET
    max_events_limit = (10000),
    max_memory = (4096)
)
WITH (
  MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF
)
GO

Msg 25623, Level 16, State 3, Line 14
The event action name, "object_name", is invalid, or the object could not be found

thanks for the reply Erik I can collect these :- <action name="client_hostname" package="sqlserver"> <type name="unicode_string" package="package0"></type> <value><![CDATA[XXXXSRV576]]></value> </action> I can't collect this (at least as a single item) <data name="object_name"> <type name="unicode_string" package="package0"></type> <value><![CDATA[PaymentSvc_ListExpirySettingsAndDeposits]]></value> </data> so the 1st is called action name but the 2nd is called data name - half an answer. Looking at the create event session syntax now — Stephen Morris - Mo64
– Stephen Morris - Mo64, Commented Jun 5 at 11:56
I've modified my session definition a bit CREATE EVENT SESSION [xetest] ON SERVER ADD EVENT sqlserver.rpc_starting( ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name )) /*, sqlserver.object_name*/ ADD TARGET package0.ring_buffer(SET max_events_limit=(10000),max_memory=(4096)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO — Stephen Morris - Mo64
– Stephen Morris - Mo64, Commented Jun 5 at 12:03

Charlieface · Accepted Answer · 2025-06-05 15:05:03Z

sqlserver.object_name is not a valid Global Action name. object_name is a field in the module_start event, you don't need to specify it in the ACTION list, it's already added to the event.

If you script this out using SSMS, you will see that it's not a global field option.

I have no idea why you'd ever want to do a divides_by_uint64 predicate on session_id but I'm sure you have your reasons.

Furthermore, from your comment, it seems you don't really understand how the XML works.

The below is an ACTION field, and the XML node name is action not action name. The name is an XML attribute.

<action name="client_hostname" package="sqlserver">
  <type name="unicode_string" package="package0"></type>
  <value><![CDATA[XXXXSRV576]]></value>
</action>

Here, data is the node name, because this is normal event data, not a global field.

<data name="object_name">
  <type name="unicode_string" package="package0"></type>
  <value><![CDATA[PaymentSvc_ListExpirySettingsAndDeposits]]></value>
</data>

A typical script to parse out the data would look like this

DECLARE @xml xml;
SELECT TOP (1) @xml = CAST(st.target_data AS xml)
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets st ON st.event_session_address = s.address
WHERE s.name = 'xetest'
OPTION (FORCE ORDER);

SELECT
  timestamp       = x.evt.value('@timestamp','datetimeoffset'),
  client_hostname = x.evt.value('(action[@name="client_hostname"]/value/text())[1]','varchar(255)'),
  object_name     = x.evt.value('(data[@name="object_name"]/value/text())[1]','sysname')
FROM @xml.nodes('RingBufferTarget/event') x(evt);

like a mod 10, to sample a subset of events, really good on a busy server — Stephen Morris - Mo64
– Stephen Morris - Mo64, Commented Jun 5 at 15:00
Yes I eventually reached a similar conclusion amongst my xe confusion. Fortunately the XESmartTarget code simplifies things loads — Stephen Morris - Mo64
– Stephen Morris - Mo64, Commented Jun 5 at 15:02

Stack Exchange Network

SQL Server Extended Events - collecting SP Object_name

1 Answer 1

Hot Network Questions

SQL Server Extended Events - collecting SP Object_name

1 Answer 1

Related

Hot Network Questions