Post reply

Stored Procedure Intermittent Problems

  • June 24, 2008 at 6:41 pm

    #381360

    I have a stored procedure that usually take around 45 mins to complete and involves 2 temporary tables. I have scheduled it to run ovenight and this worked well for about a week, but now it takes longer than 4 hours and seems to just hang.

    If I stop the SP and run it again it completes in about 15 mins. If then run it once more it is back to 45-50 mins.

    What could cause such behaviour?

  • June 24, 2008 at 9:09 pm

    #833678

    Caching an old execution plan and/or parameter sniffing could be the problem. Add the WITH RECOMPILE option to the proc and see if that problem goes away.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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