I am looking to implement an auditing process on an instance I have on SQL Server 2017. However, I am not finding a complete conclusion on which auditing feature would be best to use for a DB with a lot of transactions. I only need to audit the DML statements and not the values (e.g. I would need to know about an UPDATE statement and who performed it and on which table but I do not need to know what the value of the column was before the update).
From my research I have excluded traces and extended events due to their performance impact.
I have also excluded change tracking since it cannot be used with partial containment and I have some DBs which are set to partial containment.
I am currently mostly between using SQL Audit feature (i.e. using server and database audit specifications) and triggers. I do understand that both the above methods will cause some overhead and that the performance impact depends on how much activity I am auditing, but given the same activity being audited (e.g. selects, inserts and updates on 10 tables), which one will cause the least performance impact between these two? I am planning to use an audit file (locally or on a separate fileshare) in case I use the SQL Audit feature (and not in the windows log file).
From my research I have also concluded that triggers have to be maintained with every schema change as well, so if SQL Audit feature does not cause a massive difference in performance (compared with triggers) I will go with it.
Also I am not sure whether change data capture or temporal tables will be a better solution compared to SQL Audit feature/triggers.
Any experience with these auditing methods and their performance impact would be greatly appreciated as information about this is highly limited.