Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trigger Issue...


Trigger Issue...

Author
Message
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
Hi,

Sorry, just saw the message now (spent the day at a MS workshop).
Tomorrow when I arrive at the office I'll post the 2 trace data:
1. Current trigger (that uses a cursor and calls an SP)
2. Updated trigger (uses SQL directly).
I'll just catch events 12 and 45. Are there any more necessary (43, 168, ...)?

Thanks,
Pedro



If you need to work better, try working less...
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 866
10 and 41 are good to include as well.

Since you are tracing a single statement (plus triggers), sp_sqltrace can be useful. It's available on my web site:
http://www.sommarskog.se/sqlutil/sqltrace.html

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
Hi,

Just added an Excel file with the full trace with the original trigger and the changed trigger (that should be faster, and in "simple" UPDATE tests to the table it is much, much faster).
I also added a 3rd sheet with a side by side comparison of the code I find strange...
The statement UPDATE AA SET QtReservada = 0 FROM Inserted I INNER ... executes inside a trigger and fires another trigger, I don't know if this might be the problem source (trigger inside triggers is badddd).
But the truth is that with the slow ArtigoArmazem trigger (the 1st on the SQL script attachment) it's much faster than the improved trigger (2,25ms vs 55,7ms).
So I made a test... I changed the new trigger and added a RETURN statement right at the top.... same result...
The I removed the rest of the trigger code, just left the RETURN... fast again....
Does SQL have an issue with the WITH statement or something??! I made a test and removed the WITH and replaced it with a subquery but still slow...
It's the same database, I just keep changing the trigger code and executing the traces so nothing to do with indexes, or whatever... Statistics are updated, indexes full rebuild...

What can be causing this issue with the triggers?!
Further on the trace the trigger is used again and the new one is faster with less reads, since it's used... In this case it's used but does nothing since the StkActual column isn't updated...

Thanks,
Pedro



If you need to work better, try working less...
Attachments
side by side trace.xlsx (2 views, 172.00 KB)
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 866
If you look closer the in the after-trigger trace, you will find that it is only the first invocation that takes a longer time. Furthermore, if you look close at the times, you will find this:

This statement fires the trigger:

UPDATE AA SET QtReservada = 0 FROM Inserted I INNER JOIN LinhasDoc LD ON LD.Id=I.IdLinhasDoc INNER JOIN ArtigoArmazem AA ON AA.Artigo=LD.Artigo --BID: 583194

It starts executing at 2013-09-20 10:44:00.187

The first command in the contested trigger is SET NOCOUNT ON, and this command starts executing at 2013-09-20 10:44:00.240. (You need to look at the SET NOCOUNT ON that are a few lines above, since you have xxx:Completed events.

This is typically what you see when a module is compiled. Since the new trigger includes a more complex query than the old trigger it takes longer time to compile, but if the plan stays in cache, this overhead can be neglected.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
Erland Sommarskog (9/20/2013)This is typically what you see when a module is compiled. Since the new trigger includes a more complex query than the old trigger it takes longer time to compile, but if the plan stays in cache, this overhead can be neglected.


Thanks, I noticed that when I removed the DBCC commands the execution with the new trigger was faster, but for performance measures I always do the DBCC... Or should I just do the DBCC for the data cache (if there's just that one)?
Maybe just http://technet.microsoft.com/en-us/library/ms187762.aspx.

Thanks,
Pedro



If you need to work better, try working less...
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 866
The important thing is not whether you do DBCC or not, the important thing is that you understand the ramifications of what you are doing.

For instance, if you want to measure the performance of a query that you expect to run regularly and to have the one and same plan, you should not empty the cache and measure performance from a cold cache. It may be a good thing to empty the cache, if you are testing on your workstation, and set a low limit for Max Server Memory (to prevent SQL Server from taking over the machine). But in this case you should discard the first execution.

On the other hand, if you have a query which you expect execute only once per day, you may prefer to measure from a cold cache, because that is likely to be the actual production scenario.

In this case, the issue was about compilation. Since you are trying different triggers, the first execution will always include compilation, so whether you clear the plan cache does not really matter. What is important is that you should discard the first execution. If you have also cleared the buffer cache, SQL Server will need to read the statistics table into memory which may add insult to injury. You should still discard that first execution.

I should add that compilation times should not always be discarded. An obvious case is if a query has OPTION (RECOMPILE), in which case it will be recompiled every time. But there are also other situations where recompilations are likley, for instance queries with temp tables (because the statistics for the temp table gets updated).

Measuring performance is by no means trivial. There are plenty of traps to walk into. I know - I fallen into more than one myself.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
Thanks for helping to understand the times... Just "forgot" the trigger, SPs, functions,... are compiled the first time they're executed...

I'll do a CHECKPOINT and DBCC DROPCLEANBUFFERS to free the data but keep the execution plans and compiled SP and triggers, since that's what will happen.

Thanks again,
Pedro



If you need to work better, try working less...
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 866
PiMané (9/23/2013)
I'll do a CHECKPOINT and DBCC DROPCLEANBUFFERS to free the data but keep the execution plans and compiled SP and triggers, since that's what will happen.


Again, since you are replacing the trigger between the tests, you will see compilation first time anyway. Just discard the first execution.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
Erland Sommarskog (9/24/2013)
PiMané (9/23/2013)
I'll do a CHECKPOINT and DBCC DROPCLEANBUFFERS to free the data but keep the execution plans and compiled SP and triggers, since that's what will happen.


Again, since you are replacing the trigger between the tests, you will see compilation first time anyway. Just discard the first execution.


Yep. that's what I did. I'm making 10 runs each time and ignoring the 1st execution.

Thanks for all the help,
Pedro



If you need to work better, try working less...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search