snippetsqlMinor
SQL Server Extended Events - How to filter statements by Table?
Viewed 0 times
eventssqlstatementsextendedfilterhowservertable
Problem
I am curious, is it possible to filter the SQL Statement by table (table id or something)?
What I can think of, is to naively add another 'Statement' with Operator 'like' '%MyTable%'. Is that any 'Field' to filter the Table?
What I can think of, is to naively add another 'Statement' with Operator 'like' '%MyTable%'. Is that any 'Field' to filter the Table?
Solution
The
It seems like you are trying to capture all the statements that change the data in one of your tables: you can capture that kind of information using the
Unfortunately, this will produce a large number of events, of which you probably want just one. You can overcome this problem by capturing the events in a histogram target or by streaming the events with the streaming API.
I blogged about something similar here: Tracking Table Usage and Identifying Unused Objects.
Of course, if you're on Enterprise Edition, you can always use an Audit for this purpose.
Hope this helps
sql_statement_completed event does not have an object_id or object_name field you can use to filter the events you are capturing. It seems like you are trying to capture all the statements that change the data in one of your tables: you can capture that kind of information using the
lock_acquired event and filtering for IX or X mode and the object_id you want to include.Unfortunately, this will produce a large number of events, of which you probably want just one. You can overcome this problem by capturing the events in a histogram target or by streaming the events with the streaming API.
I blogged about something similar here: Tracking Table Usage and Identifying Unused Objects.
Of course, if you're on Enterprise Edition, you can always use an Audit for this purpose.
Hope this helps
Context
StackExchange Database Administrators Q#152173, answer score: 4
Revisions (0)
No revisions yet.