Post reply

urgent help required on proc performanc issue

  • July 18, 2012 at 3:16 am

    #392769

    on production server one procedure geting stuck on a same point again and again.

    weired thing is when we kill that spid and then start procedure again...it runs like a rocket.

    it is happening everyday.

    this proc is in between nightly batch cycle so there are lot of procedures that runs before this and updating/inserting data.

    we have already tried putting sp_updatestats/sp_recompile just before starting that procedure.

    can anyone give me a clue what might be happing here??

    [font="Arial Narrow"]Failure is not the worst thing. The worst Thing is not to try[/font]

  • July 18, 2012 at 3:24 am

    #1514085

    Could you please get us the execution plan?

  • July 18, 2012 at 4:16 am

    #1514104

    Capture both the execution plans (slower and faster) and upload them here.

  • July 18, 2012 at 4:19 am

    #1514107

    Also, set statistics time and io on and get those results.

    May be parameter sniffing issue!!!

  • July 18, 2012 at 4:22 am

    #1514108

    Not sure how the above posts would help at this point..

    I would focus on the query that is causing the blocking lock rather then on the one that is blocked. Look up the article Brad McGehee identify blocking with profiler.

Viewing 5 posts - 1 through 4 (of 4 total)

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