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.