May 22, 2011 at 1:06 pm
Is there a way in SQL Server 2005 to see how often a trigger or all the triggers in a database are used? My goal to find triggers that are not used and to also find triggers that are overly used.
Any and all help will be greatly appreciated.
May 22, 2011 at 3:39 pm
I am not sure I follow what you are trying to identify. Triggers are fired when the table they are associated with are either updated, inserted or deleted.
If you have a trigger that is defined for insert on a table and that table is never inserted into - ever, then that trigger would never get executed. The same for all other actions.
So, if a trigger exists on a table - it is there for a reason and will be executed when that action is taken on the table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 22, 2011 at 3:51 pm
Thanks for the reply Jeff. I completely understand what you are saying. I have a PM that thinks there are un-needed triggers in a database that are causing performance issues. I was hoping to somehow get a list of triggers and how often they are used. From there I was going to investigate the triggers to see how they could be improved and even work with the developers to see if they are even needed.
We had an issue, a couple months back with a trigger that was firing a lot, I was told to disable it. After disabling the trigger, I made the developers aware and no one seemed to care (for lack of a better term). He is now wanting me to try and identify more of these "problem" triggers.
I have googled this and have not had much luck, I was hoping someone out there maybe had some special query or some kind of way to look at the activity of a trigger.
Thanks Again...
May 22, 2011 at 3:59 pm
Well, there is no way to determine the activity on a trigger solely. You have to look at the activity on the table which will tell you what activity you will see on the trigger.
As to finding bad triggers - take each one and review. No other way to do it, except to review each and every single trigger, identify what that trigger does - and determine if it can be optimized. During the review, you may find triggers that can be replaced with a constraint - or even triggers that are just useless.
My best advice, roll up the shirt sleeves and start digging in.
BTW - just disabling a trigger may be causing a lot of issues with the system if you have no idea what the purpose of the trigger is for. I would recommend going back over that trigger and reviewing it to understand the purpose. You may find that it is required and by disabling it you are now causing issues with the data - or that you have disabled required audit trails.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 22, 2011 at 4:19 pm
You could setup an audit table to identify how often a trigger is fired and how many rows are inserted/deleted.
But this would require to touch each and every trigger. If you do that, you could check what each trigger does. Based on that you might already identify some triggers that are "unsafe" (e.g. designed to deal with one row only) or written in a way to cause performance issues (e.g. any kind of loops). The major task is to identify what each trigger does (or is supposed to do).
Basically, all I can do is to second Jeffrey: roll up your sleeves. 😉
May 23, 2011 at 8:18 am
How about a trace? I believe if you trace 'Statement Complete' you eventually may be able to see all trigger activity (by Object name/type).
Tracing to that level however, may cause significant performance loss while active.
May 23, 2011 at 10:18 am
ofirk (5/23/2011)
How about a trace? I believe if you trace 'Statement Complete' you eventually may be able to see all trigger activity (by Object name/type).Tracing to that level however, may cause significant performance loss while active.
Either a trace or an audit will show you how often the trigger is accessed, but that is not really the important issue here.
The thing to identify is whether or not the trigger is valid and well written. If the trigger is not valid, then it can be disabled. If it is valid, then it needs to be optimized to perform as well as possible.
The only thing a trace will help identify is any calls to the trigger that take a long time (duration). However, you have to be careful with this and identify how many rows are affected by the call. Just because the trigger takes several seconds - does not mean it is either badly written or needs to be optimized. It could be taking that long because it is affecting millions of rows.
After spending all that time gathering data, my guess is that you will find that you have to review them all (or most of them) anyways. Might as well not waste the time auditing or tracing, dig in and start reviewing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 23, 2011 at 10:49 am
Jeffrey is completely correct. The intention of the trigger might not seem to matter in a transaction, but it could be a disaster lurking if you disable it.
Imagine you have a trigger that decrements the inventory reorder level for each sale. When the level reaches 10, a separate process orders more items. If you disable the trigger, nothing appears to be failing, orders still process and all works. However at some point, depending on inventory, you'll run out of stock because the reorders cease.
A PM that says triggers just need to be disabled, without a thorough understanding of why the triggers exist, is an idiot, and incompetent. The triggers might not be needed, perhaps they were there for auditing that isn't needed, or they enforced referential integrity, which is not handled by cascades or declared FKs, but without knowing what the trigger does and why it's there, DO NOT disable triggers.
As Jeffrey suggested, go find out what they do and why.
May 24, 2011 at 12:09 am
If you are having performance problems, you should investigate the performance issues.
BBlamingthe triggers may not help at all, you could be wasting your time on it (PM's don't like wasting of time...;-)).
If your PM just wants to have order in the database you have to check each trigger individually, because the number of activations is irrelevant to the functionality.
As for performance, your PM guesses the problems is caused by triggers. Actually, there is no need to guess.
You may want to check out the SSMS reports (Right Click the server on Object Explorer -> Reports -> Standard Reports -> select one of the Performance Reports).
With the help of the reports, you can find the most CPU/Time/IO consuming queries, and see if you can mend it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply