9

I'm using extended events to trace the statements used by our application. I have created a session that will collect the information that I want, except that I would also like to know the actual values of the parameters that was used.

I am performing this trace on a non-production environment and any performance hit by the trace is acceptable.

All the queries originate from Hibernate and have been parameterized in the form of:

SELECT a, b, c From Customer where CustomerId = @P0

This is my session setup.

CREATE EVENT SESSION [TracingForStatements] ON SERVER 
ADD EVENT sqlserver.sp_statement_completed ( 
    ACTION (    
        sqlserver.session_id,
        package0.collect_system_time,
        sqlserver.transaction_id,
        package0.event_sequence
    ) 
    WHERE ( 
        sqlserver.database_id=555
    ) 
) 
ADD TARGET package0.ring_buffer(SET max_memory= 128000)
WITH (EVENT_RETENTION_MODE = NO_EVENT_LOSS, 
      MAX_DISPATCH_LATENCY = 1 SECONDS)

How can I capture the actual values of the parameters that was used in the captured statements? Is it possible?

-- Edit (Workaround): Changing the event to rpc_completed gives me the complete sql command(including all parameter values) called from Hibernate. For other cases it would still be good to know how to capture the parameter values, if it is possible.

  • Check whether sqlserver.rpc_completed provides complete T-SQL including parameter values. – Ivan Stankovic Jul 28 '14 at 17:42
  • @IvanStankovic, yea that is the way I had to do it. As mentioned in the question in the "Edit". – Peter Henell Aug 14 '14 at 7:01
3

Someone mentioned "I think only sqlserver.rpc_completed event will give you complete SQL command including parameter values." Please review answer below:

Capture Passed Parameter Values In Sql Server Extended Sessions

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.