Some tips for using bulk copy in SQL Server 2016
Consider using minimally logged bulk copy whenever possible.
The minimally logged bulk copy is much faster than other bulk copy methods, but to use
it you must provide all the following conditions:
1. The database option ‘select into/bulkcopy’ is set to true (by the way, using this
option causes the recovery model to be reset to BULK_LOGGED).
2. The target table is not being replicated.
3. The TABLOCK hint is specified.
4. The target table has no indexes, or if the table has indexes, it is empty when the
bulk copy starts.
Use native mode bulk copy whenever possible.
Because native mode bulk copies are generally faster than character mode, you should use
native mode bulk copy whenever possible.
You can use the UTF-8 code page with BULK INSERT.
In SQL Server 2016, BULK INSERT supports the UTF-8 code page. SQL Server versions
prior to SQL Server 2016 do not support code page 65001 (UTF-8 encoding).
Specify the number of rows per batch of data copied, if the transaction log was
filled before the bulk copy is complete.
Because each batch is copied to the server as one transaction, SQL Server 2016 commits
or rolls back the transaction for every batch. When you bulk copy large data files,
the transaction log can be filled before the bulk copy is complete. In this case,
enlarge the transaction log, allow it to grow automatically or specify the number
of rows per batch of data copied.
Try to use BULK INSERT command instead of bcp utility or Integration Services
to load data into SQL Server 2016.
The BULK INSERT command is much faster than bcp or the data pump to perform text file
import operations, however, the BULK INSERT statement cannot bulk copy data from
SQL Server 2016 to a data file.
Use the -K option of the bcp utility.
This option was first introduced in SQL Server 2012. If you specify “ReadOnly” for the -K
option the read-only access to a secondary replica in an AlwaysOn availability group will
be used.
If you load data into a nonempty table with the existing clustered and/or
nonclustered indexes, and the amount of data added is large, it can be faster
to drop all indexes on the table, perform the bulk copy operation, and then
recreate the indexes after the data is loaded.
Check the time needed to load data with dropping/recreating indexes and without
dropping/recreating indexes on your test server before run bulk copy operation
on the production server.
Consider increasing the packet_size option of the bcp utility.
The packet_size option specifies the number of bytes per network packet sent to and from
the server. The packet_size can be from 4096 to 65535 bytes with the default of 4096.
Increased packet size can enhance performance of bulk copy operations. Try to set the
packet_size option to 8192 bytes and continue monitoring.
Consider using XML format files to bulk import data into tables or non-partitioned
views and to bulk export data.
SQL Server 2016 supports the XML format file. XML format files are more flexible and
powerful than non-XML format files and easy to understand and create. So, consider
using XML format files instead of non-XML format files.
If you create a new table and bulk copy data into it, try to bulk load data first
and only after that create any indexes.
This can significantly improve performance of the bulk copy operation, because data
will be loaded into SQL Server 2016 table without any index pages creation during the
bulk copy.
Specify the number of the first and the last row to bulk copy, if you need to
bulk copy not all the rows from the specified data file.
This can results in good performance benefits, because the total amount of data
copied will be less.
If you load data into an empty table with the existing nonclustered indexes, try
to drop the nonclustered indexes, bulk load data and only after that recreate
the nonclustered indexes.
This can significantly improve performance of the bulk copy operation, because data
will be loaded into SQL Server 2016 table without any index pages creation during
the bulk copy.
Specify the TABLOCK hint, if you bulk copy data into an empty table from a single client.
This can improve performance of the bulk copy operation, because this causes a table-level
lock to be taken for the duration of the bulk copy operation.
If your SQL Server box has multiple CPUs, try to divide loaded data into two or
more sources and run multiple instances of BCP on separate clients to load data
in parallel.
Because SQL Server 2016 allows data to be bulk copied into a single table from multiple
clients in parallel using the bcp utility or BULK INSERT command, try to use parallel
data loads whenever possible. To bulk copy data into SQL Server 2016 in parallel,
you must provide all the following conditions:
1. The database option ‘select into/bulkcopy’ is set to true.
2. The TABLOCK hint is specified.
3. The target table does not have any indexes.
Try to avoid using CHECK_CONSTRAINTS and FIRE_TRIGGERS hints in the bcp utility
and BULK INSERT command.
Using these hints can significantly degrade performance of the bulk copy operation,
because for each row loaded the constraints and insert triggers defined on the
destination table will be executed.
Use the ORDER hint in the bcp utility and BULK INSERT command, if the clustered
index exists on the table and the data file is sorted according to the
clustered index.
This can significantly improve performance of the bulk copy operation, because
SQL Server will load data in the clustered index order without any reorders
operations.