patternsqlMinor
Can SQL Server Bulk Copy cause timeouts when querying the table
Viewed 0 times
canthetimeoutssqlbulkqueryingwhenservercausetable
Problem
In SQL Server 2008 R2, can using
Specifically could it cause the queries to time out?
The table being queried has millions of rows.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#62698, answer score: 6
Revisions (0)
No revisions yet.