How to check the performence of a storeprocedure?

vijaykumartaduri · March 26th, 2007, 02:11 AM

Hi,

Please let me tell how can i check the performance of a SP?

Ex: One of my existing SP was retived result from db so fast, but i dont know now it is retriving result but taking so much time. How should check where exactly the problem is?

Thanks,
Vijay Kumar Taduri

anubhav.kumar · March 26th, 2007, 07:22 AM

Hi,

Use the sql Server profiler, create a new trace and opt for the performance parameters u wish to monitor[SQL batches in ur case] , run the trace on the server and fire your SP, ypu will get useful info from this!

Anubhav Kumar

happygv · March 29th, 2007, 01:15 AM

There are many factors affecting the performance of SP. May be it worked fast then, because there were less data and not the same works slow bcoause of more data available. May be you can fine tune the SP to some extent knowing which causes it to slow down. Or check the table and have the proper indexes in place that will boost the SP's performance. Still indexing should be done carefully understanding its need and impact.

cheers.

_________________________
- Vijay G
Strive for Perfection