April 17, 2018 at 2:22 am
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
April 17, 2018 at 3:48 am
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
April 17, 2018 at 3:57 am
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 = ...
April 17, 2018 at 4:00 am
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
April 17, 2018 at 4:03 am
Thank you again
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy