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 |
|
dev2dev
Starting Member
48 Posts |
 Posted - 2008-04-21 : 09:37:37
|
Hi,*** Skip all and go to last 2nd para for main question ****We have our custom ETL tool that loads data in SS 2005 Enterprise Vesion using BCP. We use '-h "TABLOCK"' table hint and '-b 10000' batch size options.Prior to 2005 Enterprise version (2K and 2K5 Standard Versions), we use to maintain around 10 DBs with same schema/sps and views in each of the dbs.Now, in Enterprise version we are maintaining one DB where all tables are partitioned by one column.Multiple instances might run from different workstations. Here, I want to clarify you that no instance will load data pertaining to another partition.But at the same time there will be other components running that will query the tables in a SELECT sql and all tables have NOLOCK table hint.There can be multiple instances of this component running from different workstations. Again, none of the instance will not access data pertaining to another instance (including the component that is loading data)I have no clue why we some times get deadlock issues when we run multiple instances of custom ETL tool for loading data and other component that just has select only sql.More over I get deadlocks many times only in BCP, this is a puzzle to me because i use NOLOCK table hint. This is the only place where table is populated by data and data in table neither updates or deleted.Can some one tell which of these below concurrent transactions will get conflicts and leads to dead lock?- bcping data into table of partition A
- bcping data into table of partition B
- deleting few rows of data from table of partition E
- updating few rows of data from table of partition F
- querying table of partition C
- querying table of partition D
Thanks- D |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-21 : 12:05:45
|
| Are you bcping into a table that is part of the partitioned table? If so then I suspect the partitioning has to be checked (and maybe indexes updated) so that it can cause problems.I would bcp into a new table then swap that into the partitioned table so that the bcp does not interact with the live table - also means you can do the bcp while the table is still on-line.The swap should be vey quick so you won't have much time off-line or possibility of a deadlock. That's one of the main reasons for using a partitioned taboleHave a look athttp://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
DEADLOCKs while BCPin using "TABLOCK" hint