none
Does Server level trigger reduce performance.

    Question

  • Hi All,

    I have a task to capture all the users information along with the activities/queries being fired by different users.

    My questions is will it be a bottleneck to capture all this information?

    Please suggest.

    Thanks,

    Deven


    Regards, Deven ------------------------------------------------------------ Please vote if you find any of my post helpful.
    Tuesday, June 22, 2010 1:01 PM

Answers

All replies

  • Deven,

    Triggers work synchronously and as such whatever the user wants done has to wait on the trigger. This might be trivial in some cases, but depending on the number of users and what exactly you need to capture, this could become a performance degrading. Would a different feature provide the necessary information for you such as Audit Logging?

    Hope this helps,
    Sean

    Tuesday, June 22, 2010 1:10 PM
  • Hi Sean,

    Thanks for replying. I can work it around with audit logging. I actually have over 100 active users at time.

    What do you think i should do for audit logging, any best practice that i can use?

    Thanks,

    Deven


    Regards, Deven ------------------------------------------------------------ Please vote if you find any of my post helpful.
    Tuesday, June 22, 2010 1:13 PM
  • Deven,

    I don't know of an Audit Logging BP Guide from MS yet, but Ravi Maniam has a great blog article on getting started with a few hints and practices. With 100+ users, depending on exact data specifications and logging, their connection and processing times might spike fairly high using server level and database level triggers.

    http://blogs.msdn.com/b/manisblog/archive/2008/07/21/sql-server-2008-auditing.aspx

    Hope this helps,
    Sean

    Tuesday, June 22, 2010 2:01 PM
  • Will have a look at it.

    Thanks again Sean.

    Deven


    Regards, Deven ------------------------------------------------------------ Please vote if you find any of my post helpful.
    Tuesday, June 22, 2010 2:02 PM