Post reply

Tuning on parallel processing

  • February 15, 2011 at 10:09 pm

    #1287446

    I've attached the execution lan for update statement.

  • February 16, 2011 at 6:48 am

    #1287617

    sql_butterfly (2/15/2011)

    I've attached the execution lan for update statement.

    That's half of what we need... please post the code.

    --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 17, 2011 at 10:08 pm

    #1288637

    Jeff, My scenario is simple. I have one stored procedure with lots of inserts and selects. There is just one update at the end of it updating say few thousand of records for a particular set of parameters. Now say I'm executing it parallely (at the same time) some 30 times with different set of records. What I need to do to avoid deadlock at that particular update statement, since all the instances are going to update more or less at the same time at the same table. (For thousands of updates SQL Server escalates the locks from row level to table level. Is there any chance to force it to row level every time ? I think thats going to solve the issue as every session of sproc runs for different sets of params and hence going to update that very range of particular table)

  • February 18, 2011 at 2:35 am

    #1288698

    Deadlock graph, update code, schema for any tables/indexes involved (if you have't posted before), please.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 18, 2011 at 5:59 am

    #1288759

    sql_butterfly (2/17/2011)

    Jeff, My scenario is simple. I have one stored procedure with lots of inserts and selects. There is just one update at the end of it updating say few thousand of records for a particular set of parameters. Now say I'm executing it parallely (at the same time) some 30 times with different set of records. What I need to do to avoid deadlock at that particular update statement, since all the instances are going to update more or less at the same time at the same table. (For thousands of updates SQL Server escalates the locks from row level to table level. Is there any chance to force it to row level every time ? I think thats going to solve the issue as every session of sproc runs for different sets of params and hence going to update that very range of particular table)

    Ok... final request and then I'm moving on to help someone who will help me help them... 😉

    You want to make sure your code won't deadlock under parallel use. The code is the potential source of that problem. Without seeing the entire proc and the supporting materials that could cause the problem, we're only guessing because we can't see where explicit transactions are nor tell what order INSERTs and UPDATEs are in which causes the deadlocks.

    Without any code, the best I can do is recommend you lookup "Troubleshooting Deadlocks" or just "Deadlocks" in Books Online and study it intently and then apply the lessons learned to your code.

    --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 18, 2011 at 6:12 am

    #1288769

    Jeff Moden (2/18/2011)

    Without any code, the best I can do is recommend you lookup "Troubleshooting Deadlocks" or just "Deadlocks" in Books Online and study it intently and then apply the lessons learned to your code.

    And go any buy a copy of SQL Server MVP Deep Dives and read the chapter I wrote for it on interpreting the deadlock graph.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 19, 2011 at 12:21 am

    #1289159

    Thanks Gail for all yur help. Your information links were great. I have brought down the effect of deadlock to big extent (with the help of your shared information), though not completely. I'll try to do some more changes to the code and will check if I can bring down the query timings which will help sql to hold locks on resources for comparatively less time (resulting in no deadlock)... Apart from that if any suggestions are still welcome (like using some locking hints or forcing Row ocks etc). I'm taking this problem as a challenge and a chance to learn and dive deep into the mechanism of SQL Server, thats why doing R&D.

  • February 19, 2011 at 2:44 am

    #1289165

    sql_butterfly (2/19/2011)

    Apart from that if any suggestions are still welcome (like using some locking hints or forcing Row ocks etc).

    Don't use hints unless you are absolutely certain that you know the effects.

    Only suggestion - post the code so that we can see what's going on and give you useful and effective suggestions.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 31 through 37 (of 37 total)

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