September 17, 2003 at 9:21 am
We have a job that regularly rebuilds a "data warehouse" - for lack of better words - by dropping the participating tables/ indices, recreating them and filling them with appropriate data. If the constituent procs that make up individual steps of this process are run manually as SQL in Query Analyzer, everything works fine. If they are run as one big proc we frequently see table deadlocks. I suspect that the multi-threaded nature of the engine may cause this, inspite of using the NO LOCK hint on the Select / Insert portion of this job. The amount of data being moved is considerable. If anyone had similar experiences please let me know how you dealt with it.
September 17, 2003 at 10:17 am
Can you tell which parts are producing the deadlock? I'd run the proc with a profile running and include the SQL Stmt adn the deadlock events and try to track it down. Might be able to introduce delays or rewrite to make it work.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 17, 2003 at 10:30 am
unfortunately its not my proc so I'm not sure. I will ask my collegue to try it with a profile and see where that gets us. I suppose another approach is to break it into smaller increments (jobs) and chain those
September 18, 2003 at 11:53 am
I run a similar proc in production. The deadlock may not be caused by your routine, but rather by another proc updating the table you are reading from. I run into this frequently when my data acquisition routines are doing batch inserts into the table the data warehouse selects from. The data collection insert needed to be broken up into smaller inserts to reduce the duration of locks. Remember, that the NOLOCk hint does not apply to tables you are inserting into. If two routines are trying to insert into the same table at the same time then you can "NOLOCK" all day long and it will not matter. (I found out the hard way)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply