Seeking help with Extended events behaviour

Question

I'm using Extended Events to track the procedures that are being executed on the server, along with the Hostname of its executor.

I've used the following XE script for the same:

CREATE EVENT SESSION [EXEC_SP] ON SERVER 
ADD EVENT sqlserver.sp_statement_completed
(
  SET collect_statement=(0)
  ACTION
  (
    sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,
    sqlserver.nt_username,sqlserver.server_instance_name,
    sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,
    sqlserver.transaction_id,sqlserver.username
  )
  WHERE ( [sqlserver].[is_system]=(0))
)
 ADD TARGET package0.asynchronous_file_target
 (
   SET FILENAME = N'C:\EXEC_SP.xel',
   METADATAFILE = N'C:\EXEC_SP.xem'
 )
 WITH
 (
   MAX_MEMORY=4096KB,
   EVENT_RETENTION_MODE=NO_EVENT_LOSS,
   MAX_DISPATCH_LATENCY=15 SECONDS,
   TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
 );

However, every statement inside the body of the procedure being executed constitutes a row at the target.

For instance, if the body of my procedure has 5 statements in it, the event will be fired 5 times, and there will be 5 different entries corresponding to the same at the target.

I seek just a single entry for every procedure being executed instead of multiple entries.

That is every procedure being executed should constitute just 1 row instead of multiple.

How to resolve this?

Answer 1

sp_statement_completed is doing exactly what you are asking it to : fire for every statement in a procedure.

You likely want to change that to sqlserver.module_end - but some other things in your session don't seem to match what you're after anyway, like transaction_id, when would instance name ever be different, and capturing both database_id and database_name is kind of redundant (unless you are dropping and re-creating databases constantly, you can derive one from the other, but I would argue that database name is more useful). Perhaps:

CREATE EVENT SESSION [EXEC_SP] ON SERVER 
ADD EVENT sqlserver.module_end
(
  SET collect_statement=(0)
  ACTION
  (
    sqlserver.client_hostname,
    sqlserver.database_name,
    sqlserver.nt_username,
    sqlserver.server_principal_name,
    sqlserver.session_id,
    sqlserver.sql_text,
    sqlserver.username
  )
  WHERE ( [sqlserver].[is_system]=(0))
)
 ADD TARGET package0.asynchronous_file_target
 (
   SET FILENAME = N'C:\EXEC_SP.xel',
   METADATAFILE = N'C:\EXEC_SP.xem'
 )
 WITH
 (
   MAX_MEMORY = 4096KB,
   EVENT_RETENTION_MODE = NO_EVENT_LOSS,
   MAX_DISPATCH_LATENCY = 15 SECONDS,
   TRACK_CAUSALITY = OFF,
   STARTUP_STATE = ON
 );