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)
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)
I'm having a few issues with it.
- 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?
- I would like to only capture user stored procedure execution. Is there a way to do that?
- I would also like to get the schema in which the proc is located. How would I add that?