Extended event not capturing queries

Question

Post reply

Extended event not capturing queries

SG87

SSC Eights!

Points: 827

April 17, 2018 at 2:22 am

#410426

Hi

I have set up an extended event to capture "sql_batch_completed" events, the script to create it is below.

CREATE EVENT SESSION [SXE] ON SERVER ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)) ADD TARGET package0.event_file(SET filename=N'P:\SXE\SXE.xel',max_rollover_files=(200))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

I then enabled it and we ran a report from a web server, the only thing it recorded was "SET IMPLICIT_TRANSACTIONS ON" and "IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF"

At the same time I had the following query running every 10 seconds

INSERT INTO QueryDataselectgetdate(),s.session_id,r.start_time,s.host_name, s.login_name,s.status as 'SessionStatus',r.Status as 'QueryStatus',r.command,DB_Name(r.database_id) as DatabaseName,object_name(objectid, dbid) as ObjectName,(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,       ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)          ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS current_running_sql,s2.text as full_sql_statement,r.blocking_session_id,r.wait_type,r.wait_time,r.last_wait_type,r.percent_complete,r.estimated_completion_time,r.cpu_time,r.total_elapsed_time,r.reads,r.writes,r.logical_readsfrom sys.dm_exec_requests as rinner join sys.dm_exec_sessions as son r.session_id = s.session_idCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2WHERE s.session_id <> @@SPIDAND r.start_time > '2018-04-01 00:00'

In between a "SET IMPLICIT_TRANSACTIONS ON" and a "IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF" 30 seconds later, the QueryData table recorded an actual query which was not picked up by the extended event. Can someone explain why and whether I am setting up my extended event wrong? On the same note, the query picked up had a lot of variables with no values, can the extended event capture the variable values?

Thanks

Sam

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Capture the event rpc_completed.

Batch completed is for ad-hoc batches, like what you run from Management Studio. It won't catch remote procedure calls, which is how applications send parameterised calls to the DB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SG87 SSC Eights! Points: 827 More actions · Answer 2

Thanks GilaMonster, can you also confirm if it will capture variable values? When capturing queries in the QueryData table they seem to start like (@P1 nvarchar(4000), @P2 nvarchar(4000),... And then in the Select statement you have Select... WHERE @P1 = ...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Should do, but it'll depend on exactly how the query is sent.

Usually I see stuff like this
(@P1 int) @P1 = 27; SELECT Stuff FROM Table WHERE Column = @P1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SG87 SSC Eights! Points: 827 More actions · Answer 4

SG87

SSC Eights!

Points: 827

April 17, 2018 at 4:03 am

#1987099

Thank you again