June 14, 2009 at 11:44 pm
Hi,
I have a stored procedure that is called frequently.
Sometime the SP gets completed within 0-2 seconds.
Sometime the SP takes more than 60 seconds.
Please find the detailed information:
126 instances took greater than 60 sec.
743 instances took less than or equal 60 sec.
Is there any generic guidelines to improve the performance without changing the stored procedure (We are not supporting the Business Logic...)?
Thanks for your help,
Regards,
Prakash.
R
June 15, 2009 at 1:01 am
There could be many reasons for this behavior and it is impossible to find it without any details. Here are few things that I would check. First of all I'd try to find out if the procedure behaves the same way for a known set of parameters (e.g. If I run the procedure few times with the same set of parameters will it complete at the same amount of time each time it runs or will there be a significant time difference between each time I run the procedure with the same parameters?). If I'd notice that for each set of parameters it runs for the same amount of time, I'd check the query plan to see why it works for some of the parameters and doesn't work well for other. If I'd see that even for the same set of parameters it behaves different each time, I'd check for blocking. In any case if you want to get better help, you should also post the code.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 15, 2009 at 1:17 am
Hi ,
Please take a look at this article, which will help you
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 15, 2009 at 6:54 am
If you're not already collecting performance data using a server-side trace (which you can define through Profiler, just don't run it from the GUI), you should start.
As was stated above, there are any number of reasons for variations of this type. You need to isolate the differences between the long run times and the short run times. It really could be something as simple as the short one returns very little data and the long one returns a lot.
I'd also suggest gathering data on the wait queues and states on the system. This Microsoft white paper is very good at describing how.
BTW, assume you may need to rewrite the query. Code, not structure or indexes, is the single most common performance problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 15, 2009 at 10:09 pm
Thanks everyone...
I started collecting server side trace and DMV.
Regards,
Prakash.
R
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply