We have a very large (many millions of rows) table that contains some (few million) rows with invalid values in particular columns.

A stored procedure exists to correct the invalid records but it takes a really long time to run the first time. It is significantly faster after that, as you would expect once the procedure has been compiled and the plan generated and cached. Unfortunately the procedure will probably only be executed once so we don't get the benefit of any optimization the server does during the first execution.

Is there some way to tell SQL Server to compile/generate/estimate an execution plan for a stored procedure before running the procedure?

  • 2
  • 6
    I would think that the reason for fast execution the second time is because the data has been retrieved from disc and is already in memory in the cache. Nothing to do with generating the execution plan. May 22, 2013 at 12:31
  • 2
    In 2008 ThomasStringer's suggestion would work but in 2012 it won't cache the plan generated that way. @Wile I don't see how generating the plan in advance would help anyway even if it actually does take significant time to compile. It would still be the same total time just split into two separate operations (rather than having SQL Server automatically compile the plan then execute it immediately afterwards as one operation) May 22, 2013 at 12:48
  • @MikaelEriksson I leapt straight to the assumption that the plan was cached and didn't think about the data. Thanks for reminding me that it might not be what I assumed.
    – WileCau
    May 22, 2013 at 12:58
  • @MartinSmith are you saying the estimated execution plan from the GUI is cached in 2008? I knew there would be overhead in generating the initial plan but hoped the combined time would be less than running the procedure "cold". I guess it makes sense that SQL Server probably does the estimation before the first run and replaces it with the actual plan afterwards.
    – WileCau
    May 22, 2013 at 13:01


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.