In SQL Server 2008 R2, can using BulkCopy to upload thousands of rows of data cause problems for queries taking place on the same table?

Specifically could it cause the queries to time out?

The table being queried has millions of rows.

  • 3
    See SQLServerCentral Locks caused during Bulk insert - the suggestion there from Ian YatesIan Yates is to BULK INSERT into a temp table, and then INSERT from the temp table into your target table, at which time you can use lock hints, automate INSERTS in small batches (optionally with tiny delays between each), and so on and so forth. – Anti-weakpasswords Apr 8 '14 at 4:48

It definitely could.

It requires locks just like any other insert operation. If enough locks are taken it will escalate to a full table lock (assuming the table allows it). Any insert operation like this would block anything else that was trying to read the data, unless NOLOCK was specified for those queries (which I am not recommending here).

You can actually set up a trace to see what types of locks are being taken if you are interested. Just set up a Profiler trace that gets the "Locks: Acquired" and "Locks: Escalation", and then do a bulk insert.

Word of warning, Profiler can cause a hit to performance, so I would either run the trace on a dev server, or if you have to run it in production, export the script and execute it as a server-side trace.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.