sql audit and performance impact.

Question

sql audit and performance impact.

Heisenberg 261

we want to audit a table for DELETE operations. I want to capture login/programname/time of the user who deleted the rows for a particular table. I'm thinking of using Audit functionality of sql server. (Security folder and right-click Audits in SSMS). will this option have any performance impact on sql server or created any locking?

Ref: i will be using method mentioned in below article under section "Utilizing SQL Server Extended Events"
https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/

2 answers

Your answer

Answer 1

There will not be any locking issues.

I don't know about the exact performance impact. As discussed in that article, SQL Audit is based on Extended Events, but I don't know exactly how SQL Audit uses Extended Events. What I do know is that if you have a workload that runs a lot of short quick statements, capturing sql_statement_completed in an true Extended Events session can cause a noticeable overhead, even if you have a good filter. But the events that SQL Audit uses may be more target and imply less overhead.

I would suggest that you implement SQL Audit, and pay attention to if there are any complaints in the next few days.

Also note that SQL Audit has a few options for controlling how to write to the file, which may be important at a busy site.

I should add the disclaimer that I have not used SQL Audit a lot, but I'm just applying some common sense.

Answer 2

Heisenberg 261

Thank you @Erland Sommarskog appreciate your help as always. I ve another question related to this. if at all there is performance impact with respect to extended events or running a profiler what kind of impact would it be. Does it impact disk throughput, memory usage or CPU usage?

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-01-21T22:03:13.48+00:00

I mainly had in mind the CUP usage. Disk throughput could be impacted on the disk where you have the audit file, but my gut feeling is that you would have to generate a lot of events for that to matter. After all, a DELETE operation will be one record - but the DELETE operation could affect 50 MB of data, would of course yield a lot higher disk load.

Or put it in another way: Audit will only load the disk when there is something to write. But listening and filtering may still have overhead, depending on how its done internally.

If you really want to know, take some typical operation in your system, and run it in a test environment with and without audit, see if you notice any performance degradation.

Share via

sql audit and performance impact.

2 answers

Your answer