October 19, 2014 at 9:35 am
I'm attempting some research on SQL statements that hit multiple databases. I've been comparing SQL Trace, Extended Events, and SQL Audits. A key think I've noticed with Audits is that you can set them up at the database level to monitor for any Selects, Inserts, Update, Deletes, and it records any SQL Statement that calls an object in that database. So when you have an audit setup on 2 different databases, and a SQL Script is run that joins tables from both databases, then SQL Statement is written twice to the Audit Log, but with the corresponding database name for each entry.
So far with Trace and Extended Events, it seems they can only be setup at the SQL Server Level, and for the situation listed above, only one entry is recorded, and it uses the database specified by the logins connection. So the database may have nothing to do with the statement.
The Audits would work fine except they are missing a key piece of info that I need, which is the Application name, that generated the statement. All I get is the SPID and a login name (very generic in this environment). Whereas Trace and Extended Events provide the application name. Any ideas on how to add Application name to the Audits or Create the Extended Events at the Database Level? I can join it back on SPID to some DMV data that is being collected, but that is a real pain and far from exact.
October 20, 2014 at 7:24 am
If you need to capture events per database, you can set up a filter in extended events. In fact, it's filtering mechanism is the one gigantic improvement over trace events that makes me suggestion extended events for monitoring for most people (there are a number of other improvements, but this one is really large). Unless you capture events at the statement level, you won't see cross database queries in the way (and in many cases, not even then) you do through Audit. You could just query against the information you've captured to get the cross database queries? Not a perfect solution, but the information is there.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply