SQL Server 2008- All Action Audit and Performance RRS feed

  • Question

  • Hi All,

    We have a requirement to audit selected database action.  We are planning to use All Action Audit Feature of SQL Server 2008. I would like to know if anyone have implemented it and faced any performance issue. If anyone can share the experience it would be really helpful for me.


    Madhu K Nair
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Saturday, February 13, 2010 2:00 AM


  • 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.

    Auditing in SQL Server 2008

    If there are any more questions, please let me know.


    ***Xiao Min Tan***Microsoft Online Community***
    Monday, February 15, 2010 5:59 AM