March 5, 2014 at 6:43 pm
Hi All
Facing deadlock issues in my ETL job .
The driver table , which keeps track of what datamarts ran and for what date range gets updated frequently during the etl run . There can be as many as 250 updates issued on this table in a single second.
Now this table is a heap , and there are no indexes on it .
During these updates , we encounter deadlocks causing the ETL job to fail .
Any pointers ? Will adding an index help ?
March 6, 2014 at 4:47 am
Deadlocks are fundamentally a performance problem. So, yes, indexing may help. It's hard to say without all the specifics of your code and structures, but it's pretty likely. You'll also want to ensure that you're access all the objects involved in the deadlocks in the same order. By this I mean, if one procedure calls TableA then TableB while another calls TableB then TableA, you're moving into deadlock territory, especially if these queries are not performing optimally.
"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
March 6, 2014 at 4:54 am
Maybe this will be of some help: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
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
March 6, 2014 at 1:02 pm
GilaMonster (3/6/2014)
Maybe this will be of some help: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
Or this (note: 3 part series here): http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 8, 2014 at 5:10 pm
we created a primary key on the table, and looks like, it has helped .
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply