How can I audit how many times a Trigger in sql2005 fires

  • Hi we have a legacy application running on sql2005. We suspect part of our performance issues is from the table triggers being fired so Development wants me to see if I can tell them how often the triggers in the database fires.

    I cannot find anything and was hoping someone could give me some tips.

    NOTE: I have Spotlight if anyone knows how to get it out of there that will work as well.

  • john.yori 32529 (5/6/2015)


    Hi we have a legacy application running on sql2005. We suspect part of our performance issues is from the table triggers being fired so Development wants me to see if I can tell them how often the triggers in the database fires.

    I cannot find anything and was hoping someone could give me some tips.

    NOTE: I have Spotlight if anyone knows how to get it out of there that will work as well.

    You could create an audit table and put an insert to that table inside your trigger.

    To be honest tracking how often a trigger fires seems very strange. It will fire every time there is an event that causes it to fire. If you know the trigger you can probably tell from the data assuming you have some audit columns in your tables (DateCreated, etc).

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • You can set up a trace and capture the execution of the trigger. As Sean already pointed out, it's a strange requirement. Can you expand what you're trying to accomplish?

    -- Gianluca Sartori

  • john.yori 32529 (5/6/2015)


    Hi we have a legacy application running on sql2005. We suspect part of our performance issues is from the table triggers being fired so Development wants me to see if I can tell them how often the triggers in the database fires.

    I cannot find anything and was hoping someone could give me some tips.

    NOTE: I have Spotlight if anyone knows how to get it out of there that will work as well.

    Are the performance issues having to do with INSERTs, UPDATEs, or DELETEs? If not, then it has nothing to do with the triggers.

    It would be helpful to you if Dev identified where they are having a performance issue and leave finding out "why" up to you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yes I agree completely and we are doing just that but a lot of finger pointing and this was tasked to us to see if we could do that. I anticipated that there would be no real good or logical reason to do so but thought to throw it out there regardless..

    Thanks everyone for their input.

  • SlapShot John (5/6/2015)


    Yes I agree completely and we are doing just that but a lot of finger pointing and this was tasked to us to see if we could do that. I anticipated that there would be no real good or logical reason to do so but thought to throw it out there regardless..

    Thanks everyone for their input.

    Ah... got it. Been there before. No fun there.

    If you want to know the biggest performance problems (and that's including triggers), open SSMS and then the Object Explorer window. Right click on the instance, select [Reports], and follow your nose on the [Performance] reports.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply