1

Currently I'm using the XE event sql_statement_completed to track the DML statements that are being executed and the hostname that is performing the same. However this event gets fired for each and every SQL statement, and has to be short-circuited later on, by setting the predicate to filter out statements that are NOT LIKE 'INSERT%', NOT LIKE 'UPDATE%' and NOT LIKE 'DELETE%'. The event degree_of_parallelism gets fired only for the DML statements, however it does not provide the statement, only provides the sql_text. Setting collect_statement = (1) for degree_of_parallelism returns an error stating the "collect_statement" attribute does not exist for degree_of_parallelism. Is there a way around such that the event gets fired only for DML statements i.e. INSERT,UPDATE and DELETE, and the statement that fired the event is provided too?

Below is the sql_statement_completed event script that I have to use that is not very optimal:

CREATE EVENT SESSION [XE_Demo] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
   ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
WHERE ([SQLSERVER].[IS_SYSTEM]=(0) AND (([STATEMENT] like 'INSERT%' OR [STATEMENT] like 'UPDATE%' OR [STATEMENT] like 'DELETE%')) 
ADD TARGET package0.event_file(SET filename=N'XE_Demo.xel',max_file_size=(102400),max_rollover_files=(10000),metadatafile=N'C:\XE_Demo.xem')
WITH (MAX_MEMORY=1048576 KB,EVENT_RETENTION_MODE=NO_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)

PS. I do know that sql_text also provides the statement that fired the event, however this sql_text is nothing but the DBCC input buffer and in case your statement consists of the keyword 'password' anywhere, your buffer would be flushed and you won't get access to the statement anymore.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.