Extended Events – How to capture executions for a given stored procedure along with the passed parameter values?

All we need is an easy explanation of the problem, so here it is.

How do you capture executions for a given stored procedure along with the passed parameter values?

I tried using sqlserver.module_end but it only shows the store proc execution with no param values:

CREATE EVENT SESSION [SP Executions By Name] ON SERVER 
ADD EVENT sqlserver.module_end(SET collect_statement=(1)
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.query_hash_signed,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([object_type]='P ' AND [sqlserver].[database_name]=N'MyDB' AND [object_name]=N'MyStoredProc'))
ADD TARGET package0.event_file(SET filename=N'Z:\Extended_Events\StoredProcedures_ByNameAndDB.xel',max_file_size=(5),max_rollover_files=(5))
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)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is possible only if your proc is the oproc submitted in batch. If this is case, use rpc_completed or sql_batch_completed and filter with like predicate

Nested procs is notnpossible see parameters values.

Consider logging the parameters in a table for analsis.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply