2

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.

3
  • 1
    How many INSERT or UPDATE statements occur on your server over a given timeframe on average?
    – J.D.
    May 14, 2021 at 2:43
  • cannot really quantify for now, unless I turn on the auditing and check the number of inserts and updates logged May 17, 2021 at 8:37
  • The performance impact between the options you're asking about will vary depending on how many DML statements are actually occuring, though if you're unsure about how many that is on average, then it's probably not enough to be concerned about the performance of any of the options you've mentioned. Extended Events are actually one of the lightest performance impacting options, and that's what SQL Audit leverages as well.
    – J.D.
    May 17, 2021 at 10:30

1 Answer 1

2

CDC and temporal doesn't say who did the modification.

Audit has lower overhead than triggers. Audit uses extended events under the covers.

Audit might not give you which rows where read or modified (when parameterized sql is submitted), but that wasn't a prerequisite. I. e, I'd look at Server Audit first.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.