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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.