question

sudipta_banerjee_2014 avatar image
sudipta_banerjee_2014 asked

SP running very fast but block of code from same SP taking very long to execute from SSMS

I have a stored procedure which is executing very slowly in client production environment.ON further analysis using SQL DMV queries I found a particular block of code getting stuck. However that very same SP when we are running over the same client database copy is running successfully very fast in a matter of seconds. Now as a wild guess I copied the portion of query which was getting stuck at Production environment when the SP was invoked from the application. Surprisingly when I copied the query block & tried to execute it through SSMS the query was getting stuck. Any idea what could be the reason behind such weird behavior?
sp
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
Without a lot more information, all I have is speculation for you. First, it could be that the statistics are different between your test machine and the actual machine. It could be that the default ANSI settings for connections between the two machines is different. It might be that there's additional load causing different types of resource contention between the two machines. It could be blocking caused by another resource holding locks on the needed information. Those are the areas I would check to get started understanding what's happening.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.