A way to filter out parseonly statements from extended events?

Question

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.

Zikato · Accepted Answer · 2022-09-18 20:50:16Z

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).

edited Sep 18, 2022 at 20:50

answered Sep 18, 2022 at 19:37

Zikato

5,7241 gold badge17 silver badges35 bronze badges

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
– MBuschi

2022-09-19 07:09:00 +00:00
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
– MBuschi

2022-09-22 08:54:28 +00:00
Commented Sep 22, 2022 at 8:54

Add a comment |

Stack Exchange Network

A way to filter out parseonly statements from extended events?

1 Answer 1

Your Answer

Hot Network Questions

A way to filter out parseonly statements from extended events?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions