Performace Impact for SQL Server Audit

_{Tuesday, June 1, 2010 1:07 PM}

Hello,

I would like to Implement the SQL Server 2008 Auditing. And most important issue is to audit for Database Level.
To audit every SELECT, INSERT, UPDTE, DELETE operation on the Table by anyone.
I need to look into that what is the performace's impact of SQL Server Audit if the audits is frequently setting.
And I am asked to provide proof that the performace impact for SQL Server Audit.
Are there someone have idea or suggestion, please share with me.

Thank you very much.

_{Tuesday, June 1, 2010 1:28 PM ✅Answered}

By default the audit events are written to the audit target in an asynchronous fashion for performance reasons, so it has minimal performanc impact

Targeted auditing can also aid in minimizing the performance impact. The obvious strategy is to limit auditing to the exact set of actions of interest and nothing more. The fine-grained auditing capabilities of SQL Server Audit allow auditing to be configured so that it targets specific actions, principals, and objects (down to the individual table level). As a result, resources are not wasted generating unwanted audit information. This is in contrast to SQL Trace, which typically generates a lot of records and relies upon post-filtering to provide targeted auditing.

the following whitepaper includes some hard numbers about performance impact of some workloadsin a business environments compared to SQL trace.

http://msdn.microsoft.com/en-us/library/dd392015.aspx

Also,  the following white paper discuss various implication and settings that affects the SQL audit performance

Auditing in SQL Server 2008

http://64.4.11.252/en-us/library/dd392015(SQL.100).aspx

_{Friday, June 4, 2010 2:56 PM}

Hello Chirag,

     Thanks for your reply.  It's good for me. :-)

     And if I want to get a real testing, do you have any suggestion?

    Have a nice weekend!