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

  • SlapShot John

    SSC Journeyman

    Points: 76

    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.

  • Sean Lange

    SSC Guru

    Points: 286364

    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/

  • spaghettidba

    SSC Guru

    Points: 105661

    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?

  • Jeff Moden

    SSC Guru

    Points: 993644

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • SlapShot John

    SSC Journeyman

    Points: 76

    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.

  • Jeff Moden

    SSC Guru

    Points: 993644

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

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

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