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.