Post reply

Remote execution of stored procedure is timing out

  • December 16, 2011 at 12:24 pm

    #146271

    First of all, I am not an SQL DBA, but I am as good as my company has. I wrote a stored procedure that is called from remote hosts at the end of each production shift (on a schedule). It has been working perfectly fine for the past 2 years but now all of a sudden, it is timing out. It is a large SP but the server is relatively robust for our environment so that should not be a problem.

    If I run the SP manually, it finishes without issue.

    I would imagine that there is a setting either on the client or on the MSSQL 2005 server where I can extend the timeout value, but I am unsure where this is. Also, I would like to start a trace on the processes, but again, I am not a DBA and setting the trace to capture what is going on is, well, not obvious.

    Any suggestions?

    I really do not know what to add to this post at this time, but if you want any further information, please let me know and I will provide it.

  • December 16, 2011 at 2:21 pm

    #1422731

    you could start with this

    sp_configure 'remote query timeout (s)'

  • December 17, 2011 at 3:36 am

    #1422812

    You can set the time out value but I would prefer to verify the stored procedure execution plans. I would recommend you to trouble shoot on the factors that lead the stored procedure to perform slow (may be bad execution plan after recompile). It will be a long term solution.

    Increasing the timeout will give scope to other stored procedures as well you might not wish to run for long duration.

  • December 17, 2011 at 3:50 am

    #1422813

    There are multiple possible reasons for the slow performance.

    Here are a few I can think of:

    Network issues (e.g. due to a large size of the result set), blocking due to other processes running at the same time accessing the same sources, busy server during that particular time due to newly added functionality (therwith limiting available resources for SQL Server), ...

    I second Dev to rather find the root cause instead of simply expanding the timeout value.

    A great article how to narrow down performance issues is Gails article.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 17, 2011 at 12:06 pm

    #1422856

    Thank you all for the input. I am looking into the article and also have been reviewing the SP to see what may be causing the problem.

    I appreciate all the comments and suggestions.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply