0

In order to trace my actions on app I am using sql profiler.

Works fine, I am connecting to the server, filtering on host name, and everting what I click on app will be reflected as sql code in profiler.

But when I try to achieve the same using Extended Events it doesn't work for some reason. After I start session I go "Watch Live Data", however, whatever I do on app it does NOT reflect in SSMS query window.

If I run something in SSMS it does reflect. But does not when using app.

Do you know what am I missing here?

Update:

Trace definition added:

CREATE EVENT SESSION [test] ON SERVER 
ADD EVENT sqlserver.rpc_completed(SET collect_data_stream=(1),collect_output_parameters=(1),collect_statement=(1)
    ACTION(sqlserver.database_id,sqlserver.database_name)),
ADD EVENT sqlserver.rpc_starting(SET collect_data_stream=(0),collect_statement=(1)
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text))
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)
GO

enter image description here

Update 2

If I concurrently compare it to sql profiler (snip attached) - in Xevents I would only see SQL:Batch Starting transactions, which irrelevant to me.

What would that mean?

enter image description here

Serdia
  • 4,242
  • 22
  • 86
  • 159
  • Add the trace definition to your question (right-click on the session and script session as create). It seems your app may be using parameterized queries so you need to include rpc_starting/rpc_completed events. – Dan Guzman Jul 10 '21 at 02:12
  • Thanks Dan. I already added rpc_starting/rpc_completed. But no success. Seems like it only shows `Batch Starting` transactions , which dont have host name or username or BinaryData. I am confused – Serdia Jul 10 '21 at 02:21
  • Fields that are not part of the event can be captured as actions, like you did for the database name (e.g. `ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text))`). This will allow you to filter on those. – Dan Guzman Jul 10 '21 at 09:53

0 Answers0