SQL Server Performance Forum – Threads Archive

SP hangs but run it as query and it runs perfectly

one of my SP hangs and do not return any record but if i run the SP in the form of a query it runs perfectly and returns expected data
SP reads data from a expensive table containning around 12 million records joins with some reference table to get the description then we insert this data in a temp table as we have to process with some other expensive table. SP creates around 300 locks for the SP_ID and when we run sp_who2 it returns around 12 records for the SP_ID and some show sleeping which sounds illogical, can anyone suggest what should I do?

Did you try cleaning the cache ? I think it is DBCC FREEPROCCACHE. Then first time it should load into cache and stay there. While executing the proc, run SQL profiler. Probably it is recompiling and taking long time. Ensure that you include sp_recompile event. When data modification/insert ( including temp tables ) happens within a proc, then based on threshold values recompilation occurs. If you see recompiling as the cause then try using sp_executesql for the stmt that is causing recompilation.

Check for any blocking in this case, ensure CPU is not maxed out during operation. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

does the SP hang when running from some application (ASP, C, VB, .NET, etc)
or also when run from QA,
is there a difference between running in QA from the local machine (where SQL Server is running)
or a remote system,

Does your procedure have input parameter with a default value?

]]>