|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Why sql profiler show different values for same stored procedureWhen we did stress testing, we found that for the same procedure the time taken is between 80 ms to 5000 ms. The procedure got executed 19929 times and out of which the value was > 1000ms only 13 times and many times it was between 80 to 800. Time in milli seconds was computed as DATEDIFF(ms,STARTTIME,ENDTIME). The statistics are as below >1000 - 13 times with Max ms (4486) and Min ms (1106) <=100 - remaining times with Max ms (100) and Min ms (80)>800 and <=1000 - 13 times with Max ms (950) and Min ms (813) >600 and <=800 - 30 times with Max ms (780) and Min ms (606) >400 and <=600 - 148 times with Max ms (596) and Min ms (403) >200 and <=400 - 702 times with Max ms (393) and Min ms (203) >100 and <=200 - 3465 times with Max ms (200) and Min ms (106) Based on these statistics, I have following queries 1) How do I say that the SP performs well. It takes a maximum of 4486 only once and many times the value is less an 1000 milli second. 2) Why we have a high value of 4486 once 3) Is there a way to identify the value happens first time/ last time in any stress test. (To identify when a SP gets executed for first time in stress test, I used rownumber, which is minimun for that SP). Regards Kart Possibly reasons for varying execution time:
Different execution plans. You can grab the execution plan in Profiler. Procedure is (re)compiled. You can catch such events as well in Profiler. An auto-update of statistics generated because of generation of an execution plan and statistics is out-of-date. Again, this can be caught with Profiler. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "kart" <k***@discussions.microsoft.com> wrote in message news:2D14BE79-A704-4A7D-91F2-2EB7BA26DDFA@microsoft.com... > Hi, > > When we did stress testing, we found that for the same procedure the time > taken is between 80 ms to 5000 ms. The procedure got executed 19929 times and > out of which the value was > 1000ms only 13 times and many times it was > between 80 to 800. > > Time in milli seconds was computed as DATEDIFF(ms,STARTTIME,ENDTIME). The > statistics are as below > > >1000 - 13 times with Max ms (4486) and Min ms (1106) >>800 and <=1000 - 13 times with Max ms (950) and Min ms (813) >>600 and <=800 - 30 times with Max ms (780) and Min ms (606) >>400 and <=600 - 148 times with Max ms (596) and Min ms (403) >>200 and <=400 - 702 times with Max ms (393) and Min ms (203) >>100 and <=200 - 3465 times with Max ms (200) and Min ms (106) > <=100 - remaining times with Max ms (100) and Min ms (80) > > > Based on these statistics, I have following queries > > 1) How do I say that the SP performs well. It takes a maximum of 4486 only > once and many times the value is less an 1000 milli second. > > 2) Why we have a high value of 4486 once > > 3) Is there a way to identify the value happens first time/ last time in any > stress test. (To identify when a SP gets executed for first time in stress > test, I used rownumber, which is minimun for that SP). > > Regards > Kart > > Duration is not the best way to measure the performance of a stored proc
because it includes the time taken by the client application to consume the resultset which can vary greatly depending on influences such as: (a) Network latency between the DB & the client app (especially on a WAN) (b) Business rules implemented in the app whilst it iterates over the resultset (c) General performance of the computer the client app is running on When assessing the performance of a stored proc, you're usually far better off working with the Reads measurement, as it generally provides a much better picture of how much work the procedure is performinig on the server. CPU is also a good measure to take into account.. Even when using the Reads & CPU measures though, a stored procedure can still be seen to have changing performance characteristics, most commonly for these reasons: (a) Execution of the procedure using different parameters means that the the stored procedure accesses different amounts of data. Data range type parameters are notorious for this. (b) General performance state of the server (c) Blocking (d) Recompilation of the stored procedure HTH Regards Greg Linwood SQL Server MVP Show quote "kart" <k***@discussions.microsoft.com> wrote in message news:2D14BE79-A704-4A7D-91F2-2EB7BA26DDFA@microsoft.com... > Hi, > > When we did stress testing, we found that for the same procedure the time > taken is between 80 ms to 5000 ms. The procedure got executed 19929 times > and > out of which the value was > 1000ms only 13 times and many times it was > between 80 to 800. > > Time in milli seconds was computed as DATEDIFF(ms,STARTTIME,ENDTIME). The > statistics are as below > > >1000 - 13 times with Max ms (4486) and Min ms (1106) >>800 and <=1000 - 13 times with Max ms (950) and Min ms (813) >>600 and <=800 - 30 times with Max ms (780) and Min ms (606) >>400 and <=600 - 148 times with Max ms (596) and Min ms (403) >>200 and <=400 - 702 times with Max ms (393) and Min ms (203) >>100 and <=200 - 3465 times with Max ms (200) and Min ms (106) > <=100 - remaining times with Max ms (100) and Min ms (80) > > > Based on these statistics, I have following queries > > 1) How do I say that the SP performs well. It takes a maximum of 4486 only > once and many times the value is less an 1000 milli second. > > 2) Why we have a high value of 4486 once > > 3) Is there a way to identify the value happens first time/ last time in > any > stress test. (To identify when a SP gets executed for first time in stress > test, I used rownumber, which is minimun for that SP). > > Regards > Kart > >  |
|||||||||||||||||||||||
Other interesting topics