SQL Procedure -bit funny

  • Most of our business rules are written in backend -sql procedures.

    Once in a while there are few procedures which will be very slow in execution and these will blocks all of our application users. These procedures will run mormal as soon as I add an empty space and alter the procedure and funny thing is,it will fix this issue but I know this issue will show up again.

    I am wondering why these procedures are fast as soon as alter the proc? Are these procedures recompling? Isnt sql server smart enough to recomplie as soon as change happen to underlying tables?

    I do recomplies monthly twice as part of maintainence so I dont know where to start and any help is really appreciated.

  • It sounds like recompile issues.

    A common cause of that kind of thing is if a procedure is working on data with very different distributions depending on the input parameters. You'll want to look into options like "With Recompile" in the proc script. You'll also want to look into parameter sniffing as an issue.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thank you. Will update the thread with my findings.

  • Gsquared - Thank you very much for guiding me on this.

    The issue has been identified as Parameter sniffing.I solved it by changing procedures code in such a way that SQLserver doesnt use parameter sniffing.

    Detailed info on parameter sniffing :

    http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

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

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