3

Everyday runs a sql job that loads data from bulk files and load them in auxiliary tables, to adapt and then copy to the destination table where applications read (and only read) the data.

I am focusing on a table 'Accounts' that is accessed constantly and contains over 7 million rows. The copy from the 'AccountsAux' (loaded from the bulk file) to 'Accounts' take about 10 minutes, and during that time any selection on this table gets locked.

The insert method looks like this:

INSERT INTO Accounts
SELECT * -- actually the 60 columns are here, included the primary key (not identity)
FROM AccountsAux

I've tried using WITH (NOLOCK) on the selections, but for what I've read the insertion escalates to a TABLOCK. I don't care getting incomplete results

What options do I have to avoid locking the selections during the insertion?

Improve this question
4
  • 4
    You can reduce the problem by inserting in chunks instead of running a large, single insert that takes 10 minutes...
    – Aaron Bertrand
    Feb 23, 2015 at 14:43
  • If you're unfamiliar, this article on chunk deletes (by none other than Aaron himself) explains it well.
    – LowlyDBA - John M
    Feb 23, 2015 at 15:31
  • Thanks Aaron and John, I've found that minimizing the impact of the logging is what produces the best result. That means that I've to put the db in simple recovery model, and made the inserts in chunks to avoid the sql server from logging each inserts, which block the selections I was doing.
    – Rodrigo Rivera
    Feb 24, 2015 at 18:29
  • Now the select works, although sometimes I get this message: "Transaction (Process ID 99) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.", which I believe is because of the select rows are being modified/inserted
    – Rodrigo Rivera
    Feb 24, 2015 at 18:31

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.