SQL Server incoming request queuing?

Last Post 29 Apr 2011 08:06 AM by Kenneth Urena. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
Kenneth Urena
New Member
New Member

--
27 Apr 2011 12:43 AM
Hello,
 I was monitoring a slowness issue into the database server with regarding sequential Store proc calls delay,

There is an application that call a  database stored proc into a loop just one call after the other and stops at call No. 10, when I monitor those calls from the database server side using the profiler, the endtime of call No1 is basically the same of start time for call No.2 ... with 0 mille secs delay between one call and the other (this is when database is not showing slowness issues); but sometimes in peak time the database start facing slowness and the same profiler start showing delays from 100 millisecs to 250 millisecs between one call to another. the interest part is the actual execution time of the SP is behaving almost the same (from 2-5 millisecs more from the previous profiler)

There is already in place monitor from the source server to the database server Including NetworkTraffict and driver statistics trace. 

But I was wondering if there is any way I can check  something like port saturation for the SQL server port or any wait stats or perfmon counter or profiler who can show the calls that are arriving to SQL server and not getting executed right away? 

Any suggestions I will really appreciate it.

regards
Kenneth
rm
New Member
New Member

--
27 Apr 2011 05:25 AM
What kind of events did you trace in profiler? Included performance related ones like blocking and so?
Kenneth Urena
New Member
New Member

--
27 Apr 2011 01:22 PM
Hi,
I'm tracing SQL:BatchCompleted and RPC:Completed events.
gunneyk
New Member
New Member

--
27 Apr 2011 04:57 PM
Is the call to the sp schema qualified? Meaning is it EXEC schema.sp or just EXEC sp ? If the latter add the schema. Without the schema qualification SQL Server can exclusively lock the plan while it searches for the one with the schema of the caller before it uses the default. With high concurrency calls this will serialize the executions and cause slowdown issues like this. Also if the sp gets recompiled each time the same behavior will be seen more of less. You can see recompilesin profiler as well with those events.
Kenneth Urena
New Member
New Member

--
29 Apr 2011 08:06 AM
Hi Gunneyk,
Thanks for your answer, The call in the profiler appears without the scheme, I'm going to take a look into the recompilations to see how hight is it ... thanks for your input, I highly appreciate it.
regards
Kenneth


Acceptable Use Policy
---