Sql-server – Does SQL Server Support Parallel Bulk Inserts From Multiple Connections

bulk-insertconcurrencysql serversql-server-2008sql-server-2017

Does SQL Server support parallel bulk inserts into the same database table?

Some clarifications for my particular use case:

  • The table has a clustered index composed of two distinct index keys, and two non-clustered indexes, each one consisting of a single index key.
  • Parallel here means from multiple, distinct connections. Each connection is a unique sqlalchemy.orm.session object, and each Session is inserting 20k records of data at the same time using the Session.bulk_insert_mappings() method.

The specific questions I have:

  • I am trying to understand what is happening under the hood on the SQL Server side. Is there a queue for all of the bulk inserts, and each bulk insert is executed one by one, in the order the came in? Or are all the inserts performed at the same time, in parallel?
  • We are also assuming that no two distinct connections will be attempting to insert the same records (based on Primary Key), BUT what if two of the parallel connections WERE trying to insert records that would cause a PK violation?
  • Are the answers to the questions above different if we are asking for SQL Server 2008 vs 2017?
  • Does Microsoft have any sort of documentation on this?

Best Answer

Does Microsoft have any sort of documentation on this?

Yes, lots. One particularly good reference is the Data Loading Performance Guide.

I am trying to understand what is happening under the hood on the SQL Server side. Is there a queue for all of the bulk inserts, and each bulk insert is executed one by one, in the order the came in? Or are all the inserts performed at the same time, in parallel?

There's nothing particularly special about bulk inserts. There isn't a special queue, or anything like that. Each is processed as a normal command, which will either proceed or be blocked based on concurrent locking activity. To be clear: yes concurrent bulk imports to the same clustered table from different server connections are possible.

We are also assuming that no two distinct connections will be attempting to insert the same records (based on Primary Key), BUT what if two of the parallel connections WERE trying to insert records that would cause a PK violation?

If two connections attempt to insert the same records into a unique index, one will be blocked behind the other. When the first one releases its exclusive lock, the second will throw a uniqueness violation error (unless esoteric options like IGNORE_DUP_KEY are present on the target index).

Are the answers to the questions above different if we are asking for SQL Server 2008 vs 2017?

Not materially different. SQL Server 2016 and later can achieve minimally-logged bulk inserts to an index without trace flag 610.

It can be tricky to achieve truly concurrent bulk inserts to a table with one (or more) b-tree indexes in practice. You may need to disable lock escalation for the target and/or take other actions. It is a more complex undertaking when several indexes are present. See the Data Loading Performance Guide for more details.

Related Question