October 19, 2009 at 12:11 pm
I have stored procedure that when you try to execute it will never finish. Or it takes so Long I have to stop it. However if I run the query inside the stored procedure it comes back in less than 20 seconds. Does anyone have any idea what could be the problem or what to look for?
October 19, 2009 at 12:26 pm
-did you execute the sproc via SSMS or via another app ?
What were the connection isolation level settings?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 19, 2009 at 12:37 pm
Via SSMS. The isolation levels are set to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
October 19, 2009 at 1:06 pm
the other thing I have noticed is if I explicitly set the parameters in the stored proc to a value and then execute the stored procedure it will run pretty quickly. The stored proc is set to recompile because I though that maybe I had a parameter sniffing problem but that has not resoved the issue either.
October 19, 2009 at 2:31 pm
You probably are running in a parameter sniffing issue - review the following article by Gail (follow the links for the other articles also): http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/
You can also verify the connection settings between SSMS and the application that is calling the procedures. Do you get good performance if you execute the stored procedure from SSMS? Or do you only get good performance if you execute the query by itself?
And finally, you might want to consider using OPTIMIZE FOR - not sure about that but something you should look into.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply