Post reply

Stored procedure excution plan reuse

  • December 13, 2013 at 1:52 pm

    #283591

    I have a very complex stored procedure that it takes about 45 seconds to run the first time, and after that it takes at most a second to rerun with the same parameters, or even different parameters. Every once in a while the procedure takes a long time again, because SQL Server is recompiling it. Is there a way to save the execution plan and reuse it under my control, not under SQL Server control?

  • December 15, 2013 at 8:25 am

    #1673534

    As to my knowledge you cannot overrule each and every situation where SQL Server need to recompile a sproc.

    There are numerous situations where SQL Server may "decide" to recompile a sproc.

    Just a few of them: add/remove indexes, change of data distribution/densitiy, removed from cache due to memory request, ... the list goes on.

    If this sproc is so complex, you might need to rewrite it and/or split it into several smaller sprocs with less complexity. Hard to tell if this is possible though...


    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 16, 2013 at 9:26 am

    #1673757

    N_Muller (12/13/2013)

    I have a very complex stored procedure that it takes about 45 seconds to run the first time, and after that it takes at most a second to rerun with the same parameters, or even different parameters. Every once in a while the procedure takes a long time again, because SQL Server is recompiling it. Is there a way to save the execution plan and reuse it under my control, not under SQL Server control?

    Well, there ARE ways to control which execution plan gets used, but recompilations usually happen because the existing plan can no longer be used (due to changes to database objects it references or similar reasons). Before you go trying to override SQL Server's choice of execution plan (which is usually quite good), you should first verify that it is indeed recompilations causing the proc to run longer sometimes and not some other issue (contention, parameter sniffing, inefficient code, etc.). If you're sure about the recompilations, figure out what's causing the recompilations and address that issue first.

    Jason Wolfkill

Viewing 3 posts - 1 through 2 (of 2 total)

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