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?
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.
15 People are following this question.
