Home Forums Article Discussions Article Discussions by Author Discuss content posted by Ben Kubicek Using your critical eye to see something positive RE: Using your critical eye to see something positive

  • October 18, 2018 at 10:49 am

    #2010261

    Jason- - Thursday, October 18, 2018 6:46 AM

    Jeff Moden - Thursday, October 18, 2018 12:34 AM

    ... and their strongest justification was that it was a recommended method that they got from some web site somewhere. ...
    It took the devs about a half hour to implement the suggestion that I originally gave them to replace the sp_GetAppLock code and things have been running hot, straight, and normal since.  

    So for me, some of my strong justifications in favor of specific approaches are articles and posts from some guy named Jeff Moden. (I mean that as a sincere compliment, your willingness to share your knowledge has helped me immensely, thank you). I recently came across an article that sent me down the path of using sp_getapplock to prevent a set of procedures from being run while another specific procedure is being run. I tested several scenarios and devised a solution (one that handles releasing all the nested locks and enforces a timeout error after x seconds). I've been pleased with the solution (still in dev) until I just read your comment here and now I am concerned that I'm going down the wrong path and building a sleeping bomb.

    What is the issue with sp_getapplock?

    Ah... finally got my history folders back...

    Here's a perfmon of the blocking that sp_GetAppLock was causing.  The heavy Red lines are the blocking, which normally never appear (and look at the very long period of time they went off the scale, as well!!!).  The skinny Red line is average CPU usage across all 48 CPUs.  Normally, it's at about 8% during the time of the day (always good to have a baseline) that I took that "picture".  The heavy Dark Blue kinda square looking lines are SQL Server jobs.... they normally don't appear for more than a couple of seconds.  You can see that they've been extended, as well.  The skinny Green and Blue lines are disk reads and disk writes respectively.  With 384GB on the box, they're normally quite a bit less active than on the "picture" below.  All of that went away when we stopped using sp_GetAppLock. 

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