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

Question

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

SlapShot John

SSC Enthusiast

Points: 106
More actions
May 6, 2015 at 7:15 am

#307724

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.

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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: 105732 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 3

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)

SlapShot John SSC Enthusiast Points: 106 More actions · Answer 4

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: 1004412 More actions · Answer 5

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)