SQL Server Performance Forum – Threads Archive

Locks on the DTS

I am doing a DTS to transfer data from different tables from one sql 2000
server to another. Now the DTS part is finished. I am now checking how much
impact it will be on the destination server while the DTS is running.
I saw some lock required from profiler, but can not find any help in
interprete the binary data column in the profiler. So, when DTS is transferring the data, what kind of lock it will have on
the tables on the destination server. I’ve set the batch size to be 5 rows,
I can change it to anynumber, is there any difference?
Any article about this? Thank you very much.

---

Hi ya,<br /><br />the locks that you should be seeing on the destination table are<br /><br />IX on the table i.e. an Intent Exclusive<br />and then X locks on each of the rows and any index keys<br /><br />the batch size number is your key to balancing concurrency against performance. Large batch sizes are good for performance, but not so good for concurrency as more and more row locks (and worse index locks) are acquired. I’d suggest starting with something like a batch size of at least 100 unless there are complex triggers that are fired as the data is inserted<br /><br />Haven’t seen any articles dedicated to this <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Cheers<br />Twan

---

Thanks. This is what I wished. The strange thing is that while I
am transferring a table A, I do a select query on A too, the select
is done after the all transferring batches finish. I’ve also
set the DTS priority low. Is it normal? I expected that the select
should be done in the middle of the transferring. Thanks again.

---

Have you monitored the locks during this process along with your statements passed to select the data? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

..and to achieve high speed data loads, consider removing indexes, constraints, triggers on the target table. You could temporarily remove/disable these objects and re-enable them after the data load. This dramatically improves the data load performance. When using ‘Transform Data Task’, make sure the ‘Use fast load’ check box is checked (default) in the ‘Options’ tab. If appropriate for the table, check the box against ‘Table lock’ and uncheck the box against ‘Check constraints’. Batched operations also help minimize locking. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Hi ya,<br /><br />if table A is the destination table then yes I would expect the select to be done partway through the DTS job. If you are using a smaller batch size than the whole lot in one go… You could perhaps use profiler to look at SQL<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtStarting to see what SQL DTS is executing, in particular begin tran/commit…?<br /><br />Cheers<br />Twan

---

Thanks all for the help.
Since the destination table is also working table for our customers,
so,it is not an option for me to make any schema change during the
transfer. Disable the constraint can be checked. Thanks Sayta. I used profiler to monitor the situation. When I issue the select
query in Query Analyzer, the profiles shows that the destination
server is busy bulkinsert generated by the DTS work. After all
bulkinsert finishes (I set the batch size 5), then it starts run
the select query. In Query Analyzer, the process is just hanging
there. It looke strange. I will keep monitoring and post here if I find the
clue. Thanks all, and happy new year!

---

Hi ya, The Bulk Insert statement has two ways of controlling the batch size… BATCHSIZE will do a commit in between batches (-b in bcp)
ROWS_PER_BATCH will still do one single transaction (-h in bcp) Cheers
Twan

---

Have you been able to resolve the problem you are describing below – I have the same issue here – the DTS transform causes ‘X’ locks and the select statement in query analyzer just sits and waits.

---

]]>