Post reply

Performance Issue Problem in Stored Procedure

  • February 15, 2019 at 2:08 am

    #381419

    Hi All ,

    I have one Stored procedure it has performance issue .Every 5 hours once if i compile the SP with "ARITHABORT" on then it is working fine.how to troubleshoot this kind of issue

    Thanks,
    Sai

  • February 15, 2019 at 8:04 am

    #2022152

    That condition doesn't provide any meaningful information.  Sprocs with performance problems are usually fixed by understanding the execution plan and the query, and often, adjusting the code or adding a better index, or sometimes by defragmenting a nastily fragmented index, or any of several dozen things that can help improve performance.   Just knowing that ARITHABORT was on when you recompiled your sproc doesn't give anyone anywhere near enough information to help you.   You should post your sproc and the execution plan (an actual one, not just the estimated one), as well as the sproc code, if you want useful assistance.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • February 15, 2019 at 8:36 am

    #2022156

    saravanan.a 88885 - Friday, February 15, 2019 2:08 AM

    Hi All ,

    I have one Stored procedure it has performance issue .Every 5 hours once if i compile the SP with "ARITHABORT" on then it is working fine.how to troubleshoot this kind of issue

    Thanks,
    Sai

    From the very brief description of the problem, it sounds like it could be a "Parameter Sniffing" problem.  There are many different methods to help ease the pain there.  Google for "Parameter Sniffing".  It could also be that you have a table or index where the statistics need to be updated on a more frequent basis.

    --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)

  • February 19, 2019 at 3:13 am

    #2022354

    Hi All, 
                   After google "Parameter Sniffing" i got the following solution and its working fine with following command "OPTION (OPTIMIZE FOR UNKNOWN)".

    Thanks for your help Mr.Jeff Moden and Mr.Steve

  • February 19, 2019 at 6:24 am

    #2022364

    saravanan.a 88885 - Tuesday, February 19, 2019 3:13 AM

    Hi All, 
                   After google "Parameter Sniffing" i got the following solution and its working fine with following command "OPTION (OPTIMIZE FOR UNKNOWN)".

    Thanks for your help Mr.Jeff Moden and Mr.Steve

    Excellent.  Just remember that nothing is a panacea.  There can be a cost (perhaps in the form of not getting the best execution plan for something) to using that particular flavor of OPTION and I wouldn't automatically add it to everything (if you were struck by such a notion).  😉

    --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 5 posts - 1 through 4 (of 4 total)

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