2

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?

0
5

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
 );
4
  • I merely want to track the name of the procedures that're being executed, and the hostnames that are executing them. So far I've garnered module_end event gets fired on the completion of a procedure. Are there any other cases where it is likely to be fired? Also, at the target a great many rows exist for which the sql_text column simply does not exist. Is there a way to filter them out? Some rows have sql_text column as NULL too – Ritesh Bhakre Oct 20 '15 at 14:10
  • 1
    @Ritesh I don't think you want to filter out rows where sql_text wasn't collected. It is still an event for a stored procedure firing (you could also turn on collect_statement=(1)). You should still be able to get the procedure name anyway using ObjectName = xmlEvent.value(N'(/event/data[@name="object_name"]/value)[1]', N'nvarchar(128)'). – Aaron Bertrand Oct 20 '15 at 14:55
  • A great many system procedure executions are also being tracked, even though the predicate has been explicitly set to disallow so i.e. [sqlserver].[is_system]=(0). What's the reason for this? – Ritesh Bhakre Oct 20 '15 at 16:00
  • sqlserver.is_system is checking whether the process is a system process, not whether the user is executing a system procedure. If users are executing sp_help or Management Studio executes sp_helptext, and you don't want to see those rows in the output, you'll need to filter them out by joining the output to sys.all_objects and filtering on WHERE is_ms_shipped = 0. I don't know that there's any way to filter them out up front. – Aaron Bertrand Oct 20 '15 at 16:07

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.