August 16, 2013 at 6:55 am
Trying to hone in a few procedures to identify reads and durations. The reads are undercontrol after some indexes tweaked but still seeing high durations (with low reads).
1) If I add up Duration for SP:Statement Completed - it should equal SP:Completed.:
SP:CompletedPROCCHILD 1000
SP:CompletedFUNCTIONCHILD 0
SP:CompletedPROCPARENT 2000
The parent stored proc called PROCPARENT Calls the 2 above it - PROCCHILD and FUNCTIONCHILD. Duration here should be 1000, but it seems to double.
2) Similarly - If I add up SP:StmtCompleted for a give proc. I would expect SP:Completed to equal all of the statements. Its the sum of all the parts.
Unless I am missing something which I clearly am. Is there a recompile event or something like that thats not attributable directly to a statement? If so, then I need to include that in my trace.
August 16, 2013 at 7:57 am
Are there no other statement other that PROCCHILD and FUNCTIONCHILD in PROCPARENT?
Regards
Durai Nagarajan
August 16, 2013 at 10:20 am
I am just showing SP:Completed, so no. If a proc calls other procs (child procs) then the sum of the child proc duration, reads and writes should be the same as parent proc. I have 2 events SP:Completed and SP:StmntCompleted and they should tie in nicely.
August 16, 2013 at 4:16 pm
bukester (8/16/2013)
2) Similarly - If I add up SP:StmtCompleted for a give proc. I would expect SP:Completed to equal all of the statements. Its the sum of all the parts.
Not really. If the procedure is compiled, you will not see that in StmtCompleted. Furthermore, if the procedure includes table variables, the time to set up these will not show any particular event, although time is a just a few milliseconds.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 16, 2013 at 11:37 pm
Ok, thanks. I have never hunted for a millisecond before, so I am overanalyzing what I have always dismissed or never noticed. Its a trading system and those milliseconds add up frankly when rpc calls fans out to 12 procs which in term fan out to another 6 or 7 depending on parameters.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply