Hi,
One of primary goals for SQL Server design is that Performance impact must be minimized. Based on the test, SQL Server Audit performs significantly better than a comparable SQL Trace which is used in the past SQL Server versions. You could read the “Performance Comparison” section of the following article. It shows the performance numbers for SQL Server Audit vs. SQL Server Trace in a number of real-world scenarios.
To optimize SQL Server Audit:
1. Use the asynchronous model to write the audit events to the audit target. The choice of asynchronous or synchronous is controlled by the QUEUE_DELAY option of the CREATE AUDIT DDL. By default, SQL Server Audit uses the asynchronous model and the value of the QUEUE_DELAY option is 1000, which indicates that SQL Server Audit will queue the audit record for up to one second before writing to the target. If tolerances allow, you can set this value to a larger number,
2. Use a file as the audit target.
3. To minimize the performance impact, limit auditing to the exact set of actions of interest instead of all actions.
Reference:
Auditing in SQL Server 2008
http://msdn.microsoft.com/en-us/library/dd392015.aspx
If there are any more questions, please let me know.
Thanks.
***Xiao Min Tan***Microsoft Online Community***
