principlesqlMinor
Extended events vs SQL Audit - performance implications
Viewed 0 times
eventssqlauditextendedimplicationsperformance
Problem
I would like to setup an audit trail kind of system on my database to monitor for
As SQL Server Audit internally uses Extended Events, I assume there will be some kind of overhead when I use Audit instead of Extended Events directly.
Is there any way I can do some testing to analyze which system is impacting the server more? If I could know what actually happens when any XE session is created it would help me in analyzing the impact on the server.
We considered triggers and left out that option because of overhead. But that was just decided based on information from the Internet.
UPDATE/INSERT statements on a specific table with very high activity. I have two options in front of me: using the SQL Server built-in Audit system or go with Extended Events.As SQL Server Audit internally uses Extended Events, I assume there will be some kind of overhead when I use Audit instead of Extended Events directly.
Is there any way I can do some testing to analyze which system is impacting the server more? If I could know what actually happens when any XE session is created it would help me in analyzing the impact on the server.
We considered triggers and left out that option because of overhead. But that was just decided based on information from the Internet.
Solution
One benefit of the Audit that comes to mind is that it will automatically record who turns it on and off, XE won't do that out of the box (though you might find an event that tracks XE stop/start). You may also find that the two capture different data, depending on exactly what you want.
Regarding doing some testing, you would need to have a database backup, capture a trace of the application under load, and then restore the copy while doing a replay / replay with audit / replace with XE and comparing performance data.
Which performance data? It's up to you. For some ideas - Linchi Shea did a comparison between Audit and Trace by focusing on Transactions/sec, while Kehayias did a comparison between Trace and XE by focusing on batches/sec and overall replay runtime.
I'd encourage you to read both and their comments because you should know that no matter what you do it will be open to interpretation. It's difficult to get an apples for apples comparison. Also, a trace/replay can fail to simulate load properly - for example when your application is doing lots of bulk loads from disk files that no longer exist.
But the important thing is that you try at least one thing, so you can justify your decisions, and also blog about it for the rest of us.
Regarding doing some testing, you would need to have a database backup, capture a trace of the application under load, and then restore the copy while doing a replay / replay with audit / replace with XE and comparing performance data.
Which performance data? It's up to you. For some ideas - Linchi Shea did a comparison between Audit and Trace by focusing on Transactions/sec, while Kehayias did a comparison between Trace and XE by focusing on batches/sec and overall replay runtime.
- http://sqlblog.com/blogs/linchi_shea/archive/2012/01/24/performance-impact-sql2008-r2-audit-and-trace.aspx
- http://sqlperformance.com/2012/10/sql-trace/observer-overhead-trace-extended-events
I'd encourage you to read both and their comments because you should know that no matter what you do it will be open to interpretation. It's difficult to get an apples for apples comparison. Also, a trace/replay can fail to simulate load properly - for example when your application is doing lots of bulk loads from disk files that no longer exist.
But the important thing is that you try at least one thing, so you can justify your decisions, and also blog about it for the rest of us.
Context
StackExchange Database Administrators Q#139580, answer score: 3
Revisions (0)
No revisions yet.