SP causing blocking

  • I have a simple proc that I suspect is causing intermittent blocking.  It takes a customer ID (int) as a param and returns an output param FeatureFlag (bit) indicating whether a feature in enabled.  The customer ID is the PK and clustered.  The problem is that the proc gets called 100s of times a minute, during certain times of the day. My idea is to create a covering index on customer ID and FeatureFlag and specify that index with a table hint in the proc.  There are lots of other procs that hit that table for other reasons (mostly reads) and I'm thinking that this would relieve the pressure on the clustered index and help with the blocking issue.

    Am I crazy?

    TIA

    John Deupree

  • No.

    If it's doing a lookup only (not modifying data, just reading it), you could also use WITH (NOLOCK) on the table to reduce overhead of the lookups.  I would argue that you should, although some people reflexively attack any use of NOLOCK.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I don't think that creating a covering index will help you.  Since you are using the clustered primary key as your search argument, you are not doing any lookup.  Are you sure that this procedure is causing the blocking?  When you look at the procedure's query plan do you see that it does an index seek?  If the answer for both questions is yes, you can consider using Scott Pletcher's advice and specify with (nolock) hint, but you have to understand that you'll might get a dirty read that was not committed to the database and on rare occasions you'll might not find a record that exists in the table.

    Adi

  • Is the query scanning the existing index or seeking against it? If it's a seek, and it's the cluster, I'm not entirely sure you'll see much of an improvement with a covering index. It's likely to behave exactly the same way... maybe. Testing is your buddy in these circumstances.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • John Deupree wrote:

    I have a simple proc that I suspect is causing intermittent blocking.  It takes a customer ID (int) as a param and returns an output param FeatureFlag (bit) indicating whether a feature in enabled.  The customer ID is the PK and clustered.  The problem is that the proc gets called 100s of times a minute, during certain times of the day. My idea is to create a covering index on customer ID and FeatureFlag and specify that index with a table hint in the proc.  There are lots of other procs that hit that table for other reasons (mostly reads) and I'm thinking that this would relieve the pressure on the clustered index and help with the blocking issue.

    Am I crazy?

    TIA

    How have you proven that the proc has an issue?  Have you looked at the actual execution plan?

    And new nonclustered index may help in that it would be much more narrow than the clustered index and so could take a lot less memory and IO and maybe speed.  The only way to know for sure is to first make sure you know  how to measure it without changing it and then try it.  Depending on the nature of the query, a filtered index may work even more of a treat.

    But, again, we're only speculating.  You're going to have to try things in-situ and under-fire to know for sure because, right now, I'm not sure that you actually have a problem to begin with and neither are you. 😀

     

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

  • Grant Fritchey wrote:

    ... maybe. Testing is your buddy in these circumstances.

    Preach it Brother! One good test result is worth a thousand expert opinions.

     

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

  • Some monitoring softwares will show blocking in the engine and may get you on track detangling such issues.

    If you think (b)locking is the issue, you may even want to try snapshot isolation if you do not want to experience the potential down sides of using nolock.

    But then again, snapshot isolation may also need some attention in tempdb.

    Check your alternatives and TEST IT !

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks for the replies.  I don't want to use NOLOCK because I want to be sure that the feature is enabled before kicking off the job.  A customer might have multiple jobs in a short time period and if one fails the flag gets set to disabled so that we don't have multiple failures stacking up.

    And yes we do have output from WhoIsActive showing that this is the blocking code.  the actual query plan (in dev) shows a clustered index seek.

    Unfortunately this is not reproducible in our dev or QA environments (not enough volume and other activity), and testing is frowned upon in production :). I'll see about getting some way of reproducing the frequency and number of the calls to see if I can reproduce.

    Thanks again.

    • This reply was modified 2 years, 7 months ago by  John Deupree.

    John Deupree

  • This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

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