Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kdd21
Starting Member
11 Posts |
 Posted - 2007-05-11 : 16:36:30
|
| Am working with a warehousing installation that nightly imports a massive amount of data. Before the import, indexes are dropped and afterwards, the indexes are regenerated. The "create index" steps are now being multithreaded-- distributed over several threads. However, I'm finding in cases when the create indexes are operating on the same source table, a thread may become a deadlock victim.This is on SQL Server 2000. Haven't tried it on 2005, as it's not that easy to try at the moment.So I gather that while it's indexing, since it can't afford to have the table change while indexing, it keeps it locked-- but in such a way that is blocking reads as well? I can't otherwise see how such a deadlock would occur. The threads are doing a create index on the same table but creating a different index, and generally using different columns, though there may be cases where one index is a composite field which would include a column also utilized in another index. But essentially, it's a single read-only operation from the contending table so I don't really see why a deadlock should occur.Any ideas what may be going on here? Options I might be able to use to work around the problem (other than going single-thread, preferrably)...--Sync |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-11 : 16:56:26
|
| Do you create index one by one or run bunch of scripts at same time? |
 |
|
|
|
|
|
deadlock on create index?