March 16, 2007 at 11:05 am
I have an UPDATE trigger on a table that is constantly being accessed and updated. The trigger seems to be recompiling many, many times throughout the day. When the trigger is recompiling, a COMPILE lock is placed on the table (can see this in sysprocesses table), and this is causing blocking and contention issues in my database.
Using SQL Profiler and Microsoft KB, I have determined that the reason for the recompiles is "Statistics changed (rowmodctr in sysindexes)."
The statement that seems to be causing the recompile is:
If Exists(Select 1 From inserted Where bn_skiptrigger = 1) RETURN
I have tried rewriting this as:
If ( Select Count(*) From inserted Where bn_skiptrigger = 1 ) > 0 Return
And
Declare @cnt int
Select @cnt = count(*) From inserted Where bn_skiptrigger = 1 OPTION ( KEEPFIXED PLAN )
If @cnt > 0 Return
Neither of the other methods have resolved the recompiling issue.
I am stumped as to how to go about creating a fix for this issue. Do you have any suggestions?
Thanks!
March 26, 2007 at 12:16 pm
How many seperate transactions occurr on this table at one time (not batches individual)? Also would help if we can see the entire trigger to be sure of what is going on.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply