0

If I enable Audit on production databases that have over 100 m rows and have constant DML statements going (24 hours), is that going to cause any overhead on SQL server performance or tempdb? Does that increase the .ldf log size of the database its monitoring?

Any issues this can cause that I should be aware of?

3

If I enable Audit on production databases that have over 100 m rows and have constant DML statements going (24 hours), is that going to cause any overhead on SQL server performance or tempdb?

To directly answer your question, yes. Auditing always has overhead. If you have to write down what you've done and call extra code that wouldn't normally be called then by definition it'll have overhead.

Whether that overhead is appreciable enough to cause you an issue we can't say. This is completely dependent upon:

  • How the auditing is implemented
  • What you are auditing
  • Where you are holding your audit information

There will be a different in performance using some 3rd party solution utilizing detours/iat/eat hijacking/whatever versus a trace vs security (extended events). Your mileage will vary with all of these (not all supported) choices.

Does that increase the .ldf log size of the database its monitoring?

If, for some reason, you keep the audit records in a table in that database then yes... otherwise, no.

  • Thanks much. I am planning to keep the records on the file server not in one of the databases. Just thinking of using the SQL audits available in SSMS under security. It will be auditing one table that has over 100 m rows. – Sarah Nov 15 '18 at 23:19
  • 3
    @Sarah Number of rows in the table is largely irrelevant, it is how much activity you are auditing. – Aaron Bertrand Nov 16 '18 at 0:11

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.