I would like to setup an audit trail kind of system on my database to monitor for 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.
Best Answer
I created a simple test rig to trial SQL Server Audit against triggers, and potentially other options. In my tests of inserting 1 million rows into a table I got 52, 67 and 159 seconds for baseline, SQL Audit and my trigger respectively:
Now this isn't particularly scientific but does potentially offer you one way of comparing approaches. Have a look through the script, see if it can be of use to you:
Whilst the trigger option didn't do very well here, my trigger code could be simplified depending on what you want to capture and it does allow you access to the old and new values in a fairly usable format which SQL Audit does not. I have used this technique for a lower-activity config table and it works quite well. Depending on what you want to capture you could also consider Change Data Capture.
Let me know how you get on with your trials. Good luck.