2

I am working with extended events in SQL Server 2008 to perform some monitoring. I am mostly interested in the following events:

  • sqlserver.sp_statement_completed
  • sqlserver.sp_statement_starting
  • sqlserver.sql_statement_completed
  • sqlserver.sql_statement_starting

I would like to know if it is possible to filter out these extended events according to the table(s) they affect. I have not seen any predicate that would help me perform such filtering. Since a single stored procedure might actually affect multiple tables, I am not even sure how they would do it, but someone out there might have thought of some neat trick.

Thanks in advance!

Huck
  • 23
  • 4

1 Answers1

2

Nope, there is nothing in SQL Server 2008 that will allow you to attempt to do this as a part of the session definition, but you could do it as a part of post event processing if you use track_causality=on and you add in the sqlserver.lock_aquired event for schema stability locks at the object level only and then filter on the object_id of the objects that you want to target. Then you have to do the filtering based on the activity_xref_id and you can throw out the starting/compleded events for those activity_xref_ids that don't have an associated sqlserver.lock_acquired event with them. This would work but it is complex and would capture a lot of additional noise data as well.

Come to think of it, you could also just use the sqlserver.lock_acquired event with the sqlserver.sql_text and sqlserver.tsql_stack actions to be able to do the same thing with less noise.
Are there specific data elements returned by the starting/completed events that you want to be able to correlate with accessing a specific object, or do you just want to know the SP's and adhoc SQL that access a specific object in a database?

Let me know some more specifics and I'll see if I can work up a demo that shows this for you.

Jonathan Kehayias
  • 3,402
  • 1
  • 23
  • 23
  • Hi Jonathan, thank you so much for your answer. In a few words, my clients want to perform audit on their SQL Server database through Extended Events & ETW because all their other systems will also log to ETW, thus allowing correlation. The 4 events I chose for the audit are the ones specified above in the question. Acknowledging this will generate LOADS of events, they have asked if we could reduce the scope to... only a few tables... Thus my question about filtering the events **early** by table. Do you think we are on the right track if we want to log the database activity in ETW? Thanks! – Huck Sep 08 '11 at 12:52
  • Just because you can doesn't mean that you should or that it makes sense. Writing XEvent data to ETW doesn't guarantee that you can correlate the data with other processes writing to ETW. SQL Server uses the classic provider for ETW only, not the manifest based provider that is available in Windows Server 2008 and generally used by applications. Correlation of ETW data from SQL using standard tools generally results in having event guids from SQL that you have to do a lot of manual mapping to understand, that are interspersed with other events loosely. This implementation seems wrong to me. – Jonathan Kehayias Sep 09 '11 at 22:17
  • 1
    After my investigation, I tend to agree with you. It seems the "SQL Server Audit" feature in 2008 is more suitable for our needs. Plus, seemingly built upon the extended events, it must meet our performance goals. – Huck Sep 12 '11 at 13:52