SQL Server Performance

timeout issue

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by erzats, Apr 7, 2009.

  1. erzats New Member

    Hi,I have a stored procedure that was implemented last year. This procedure is used once every month from aspnet page, to import data from csv file to sql server table and it takes less than 1 minute. It was ok last month but yesterday, when the client executed the same storproc he had a timeout (after 10 minutes). I recompiled the procedure which brought back the time to less than 1 minute. In the future my client wants me to modify the storproc by adding “with recompile” option. I would like to know your opinion on this matter. Is it a good idea adding this option? I forgot to tell you that I have restored this db in another environment and the exec time was ok. The developer told me he thinks it is normal because after a restore, all stor proc will be recompiled. Can someone explain me if really all storproc will be recompiled after a restore? Does anyone have an idea what could be the cause for the timeout?
    Server Error in '/' Application.
    Error for Main Step: Object reference not set to an instance of an object.
    Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Exception: Error for Main Step: Object reference not set to an instance of an object.

    Source Error:
    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
    Stack Trace:

    [Exception: Error for Main Step: Object reference not set to an instance of an object.]Thanks in advance for your help
  2. techbabu303 New Member

  3. satya Moderator

    This is not a SQL related problem, more you should continue the investigation from web server side of it.
  4. erzats New Member

    Maybe you’re right but why I have never had this problem in other environments and why it is gone when I recompile the storproc?Now the storproc run with the option "with recompile" but I really want to understand why this problem, why after 1 year.....

Share This Page