Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Deadlock issue

Proposed Answer Deadlock issue

  • Friday, April 20, 2012 2:11 PM
     
     
    Sign In to Vote
    0

    I am using SSIS package and loading about 4.9 million records , and it continuously failing for deadlock issue

    FYI, 

    1. The code is taking data from the work table with some Isnull function in it and dump to destinations table

    2. The worktable is joined with 56 dimensions

    3. All the dimensions have nolock

    4.Some of dimensions are used 5 or 6 times with diffrent Alias inside the join statement

    5. There are other ETL process before the insert run(e.g before the insert there is update statement , there is also another loop that insert to work table , all these run fine.)

    What possible reason could be why it failing for deadlock ?

     

All Replies

  • Sunday, April 22, 2012 12:18 AM
     
     
    Sign In to Vote
    0

    Hi Beney2009,

    Try giving the query hints HASHGROUP and/ or MAXDOP in your select and insert statements. Refer http://msdn.microsoft.com/en-us/library/ms181714.aspx for more details on this.

    Let me know if this does not help

     
  • Sunday, April 22, 2012 5:14 AM
     
     Proposed Answer
    Sign In to Vote
    0

    Hi Beney,

    The most obvious reason is concurent resource usage during ETL execution.

    The challenge is to trace and find out exact statements, which are root of the issue.

    Take a look to this article: Detecting and Ending Deadlocks. There are good tips how to capture deadlock information to errorlog or Profiler.

    When statements detected you have these options:

    1. Manage to them to run in sequnce

    or

    2. Use Deadlock preventing hints like UPDLOCK for such statements

     
  • Monday, May 07, 2012 7:10 PM
     
     
    Sign In to Vote
    0

    Hello Beney2009

    Please Select The line Precedence Constraint Editor and Select Completion Value, This procedure Finish each Step before to continue the other .... Please verify Multiple Constraint.. Select Logical OR..

    Please Visit This Link : http://msdn.microsoft.com/en-us/library/ms188447.aspx

    Best Regards :

    Leonardo Martínez P.

    Leonardo