Friday, April 20, 2012 2:11 PM
I am using SSIS package and loading about 4.9 million records , and it continuously failing for deadlock issue
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 ?
Sunday, April 22, 2012 12:18 AM
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
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
2. Use Deadlock preventing hints like UPDLOCK for such statements
- Proposed As Answer by Jerry NeeModerator Tuesday, April 24, 2012 10:05 AM
Monday, May 07, 2012 7:10 PM
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.