SQL Server Performance Forum – Threads Archive

SP hangs application on first execution

Hello I have a .Net application which invokes an stored procedure (SQL Server 2005 Express installed on the same machine). When the stored procedure is called the first time, the application hangs because the sp never ends execution and the application’s process has to be killed. But when the application is executed again, the sp runs as expected. What could be happening? The stored procedure references remote tables by means of synonyms. If the Management Studio is used instead, the sp never ends execution when invoked the first time, but the query can be cancelled. Now, if the sp is invoked first in the Management Studio first and then by the application, this does not hang (the sp executes as expected). Thanks a lot.

What’s happening is when you run it the first time, SQL Server is caching the query plan and any subsequent calls are using the cached plan. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

Thanks for yor reply. I don’t see why this query in particular should hang on the first execution. Other queries do not have the same problem; moreover, I need to stop the application from hanging when calling the sp the first time.

Can you post the full stored proc ?

Take help of PROFILER to see where it is hanging, I guess it could be while checking the remote connection as in the SP to check the remote tables etc. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>