2

I'm currently using extended events to capture query statements executed in a production environment. But the actual session is also capturing the activity of 'query parsing' done by operators to evaluate if a query is well written.

I would like to have an example of how to filter out SSMS activity of query parsing (CTRL+F5). This is used in production to check code. Ideally, code would be checked before production, but they are doing and willing to do it.

We have in place an XE solution that is working well but we need to fix a few 'human cases' like the parseonly queries.

An example of a session capturing a SQL text:

CREATE EVENT SESSION [Session]
    ON SERVER
    ADD EVENT sqlserver.sql_batch_completed
    (
        ACTION(sqlserver.sql_text)
    )
    ADD TARGET package0.event_file
    (SET
        filename = N'C:\Folder\File.xel',
        max_file_size = (2),
        max_rollover_files = (2)
    )
    WITH (
        MAX_MEMORY = 2048 KB,
        EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 3 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = ON
    );
GO

I have no fixed starting session to fix. I would like an example of XE filtering out parseonly queries.

We cannot filter out the events using host_name or client_app_name.

0

1 Answer 1

4

You might use the more specific sql_statement_completed or sp_statement_completed which are not fired on query parsing.

If that's not an option, you can add additional filters to the sql_batch_completed event.

CREATE EVENT SESSION [Session] ON SERVER
ADD EVENT sqlserver.sql_batch_completed
(
    ACTION(sqlserver.sql_text)
    )
)
WHERE NOT
(
    row_count = 0
    AND logical_reads = 0
    AND writes = 0
)

Since the parse-only query is not executed, it will have no activity. The downside is that you might filter out other valid batches like RAISERROR ('Custom error', 1,1).

2
  • Thank you. I will check them all. Probably fitering out error statemts is not an option as we also want to collect statements not allowed by rights.
    – MBuschi
    Commented Sep 19, 2022 at 7:09
  • It seems that using sql_statement_completed and sp_statement_completed make the day. I'm have to check the whole solution to evaluate if this change don't go to loose anithing else.
    – MBuschi
    Commented Sep 22, 2022 at 8:54

Your Answer

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

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