patternModerate
What is the proper event in Extended Events to track stored procedure executions?
Viewed 0 times
storedthetrackwhateventsextendedprocedureexecutionsproperevent
Problem
My goal is to track executions of a single stored procedure using Extended Events
In the event selection list, I can find following:
Which one should I chose ?
I only need a distinct execution count
In the event selection list, I can find following:
sql_statement_completed
sql_batch_completed
sp_statement_completed
rpc_completedWhich one should I chose ?
I only need a distinct execution count
Solution
Include the
Note that if the same stored procedure name exists in more than one database or schema, the different procs will be counted as one with this method. An additional filter on the database can be used to limit counting to only the desired database.
module_start event filtered on the stored procedure name and a histogram target bucket based on the object_name field. This will capture and aggregate the execution counts every time the stored procedure is executed, whether it is called directly or indirectly (i.e. called other stored procedures and triggers).CREATE EVENT SESSION [module_start_execution_summary] ON SERVER
ADD EVENT sqlserver.module_start(
WHERE ([object_name]=N'YourStoredProcedure'))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.module_start',source=N'object_name',source_type=(0))
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=ON);Note that if the same stored procedure name exists in more than one database or schema, the different procs will be counted as one with this method. An additional filter on the database can be used to limit counting to only the desired database.
Code Snippets
CREATE EVENT SESSION [module_start_execution_summary] ON SERVER
ADD EVENT sqlserver.module_start(
WHERE ([object_name]=N'YourStoredProcedure'))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.module_start',source=N'object_name',source_type=(0))
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=ON);Context
StackExchange Database Administrators Q#240184, answer score: 12
Revisions (0)
No revisions yet.