Extended Event To Capture Proc Execution

• RonMexico

  Hall of Fame

  Points: 3414

  More actions

  April 29, 2020 at 12:14 pm

  #3747270

  I configured an extended event to capture the time procedures are executed and below is what I have so far

  CREATE EVENT SESSION [PROC_EXEC] ON SERVER 
  ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1),collect_statement=(0)
      ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.username)
      WHERE ([package0].[equal_uint64]([object_type],(8272)) AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[database_name],N'master, model, tempdb, msdb, UserDB1, UserDB2')))
  ADD TARGET package0.event_file(SET filename=N'C:\PROC_EXEC.xel',metadatafile=N'C:\PROC_EXEC.xem')
  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

  I'm having a few issues with it.

  1. You can see I'm trying to exclude the system databases and a couple of user databases but the user databases aren't being filtered out. Is something wrong with the syntax?
  2. I would like to only capture user stored procedure execution. Is there a way to do that?
  3. I would also like to get the schema in which the proc is located. How would I add that?

• Site Owners

  SSC Guru

  Points: 80373

  More actions

  April 30, 2020 at 1:10 pm

  #3747661

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.