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:

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?

  • 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 '15 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 '15 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 '15 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 '15 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.