Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Trigger Issue... Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 1:53 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1496575
Posted Thursday, September 19, 2013 3:41 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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
Post #1496620
Posted Friday, September 20, 2013 4:15 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...


  Post Attachments 
side by side trace - scripts.txt (1 view, 2.71 KB)
side by side trace.xlsx (2 views, 172.64 KB)
Post #1496783
Posted Friday, September 20, 2013 3:35 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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
Post #1497061
Posted Saturday, September 21, 2013 1:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1497119
Posted Saturday, September 21, 2013 4:30 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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
Post #1497131
Posted Monday, September 23, 2013 4:01 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1497312
Posted Tuesday, September 24, 2013 6:01 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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
Post #1497787
Posted Tuesday, September 24, 2013 7:05 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1497832
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse