By aleksey vitsko


2019-06-10 10:14:37 8 Comments

My goal is to track executions of a single stored procedure using Extended Events

In the event selection list, I can find following:

sql_statement_completed
sql_batch_completed
sp_statement_completed
rpc_completed

Which one should I chose ? I only need a distinct execution count

1 comments

@Dan Guzman 2019-06-10 10:42:16

Include the 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.

Related Questions

Sponsored Content

3 Answered Questions

[SOLVED] Expensive Query not showing in extended events trace

1 Answered Questions

[SOLVED] Monitor stored procedure statistics with extended events

1 Answered Questions

Sponsored Content