SQL Server Performance

An stored procedure need to be recompiled too often

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by EMoscosoCam, Aug 26, 2008.

  1. EMoscosoCam Member

    I have a job that calls sp_recompile for a given stored procedure every morning at 9am. Sometimes, I have to call sp_recompile again at 6pm because the application that calls it have a "query timeout" error. I wonder that stored procedure recompilation is not enough. What can you suggest me?
    Thanks a lot.
  2. preethi Member

    I wonder what operation happens in the code and underlying tables which prevents SQL Server from recompiling
    Normally, If SQL server thinks that the current execution plan is aged, it will recompile again. If the recompilation cost is too high and the procesure is called much frequently, it may take longer time to recompile. If you write your code too genaric (supporting multiple scenarios by using OR and optional parameters) the cost may be very high and it will not recompile soon.
    Can you do some more research on what statement causes the recompiled plan to work better? You can check the previous and current execution plans and see the differences.
  3. satya Moderator

    Agree with Preethi here that you need to find out why you have to issue recomplie statement every morning. I believe there might be a missing index or out of date statistics on the database/tables which is causing slow performance or even timeout in your case, so you have to find out best way to fine tune the performance.
    Refer to http://sqlserver-qa.net/blogs/perftune/archive/2008/02/05/3182.aspx blog post that talks about how you can go about it.

Share This Page