Introduction

There is no single best SqlBulkCopy.BatchSize for every workload. The practical answer is to start with a moderate value, often around 1000 to 5000 rows, then test against your real table shape, network path, indexes, and transaction requirements.

What BatchSize Actually Controls

SqlBulkCopy streams rows to SQL Server efficiently, but it can still commit work in chunks. BatchSize tells the bulk copy operation how many rows to send before one batch is considered complete.

That setting affects several things at once:

• how often SQL Server commits progress
• how large each unit of network and log activity becomes
• how much work is lost if a batch fails
• how long locks may be held during each chunk

Because those costs vary by schema and environment, batch size tuning is always contextual.

Why There Is No Universal Number

A narrow staging table with no indexes can tolerate much larger batches than a heavily indexed production table. A local import from the same machine behaves differently from a bulk copy across a busy network. Recovery model, triggers, foreign keys, and concurrent readers all change the optimal point.

That is why recommendations such as "always use 50000" are usually unreliable. A large batch may improve throughput in one system and make another system slower because transaction log pressure or blocking becomes the dominant cost.

A Good Starting Range

For many ordinary imports, 1000 to 5000 rows is a safe place to begin. It is large enough to reduce per-row overhead, but small enough that failures, locks, and transaction log bursts stay manageable.

If the destination is a dedicated staging table and the server has plenty of headroom, it is reasonable to test larger values such as 10000 or 20000. The key word is test. Do not assume bigger is automatically better.

Example Configuration

1using System.Data;
2using Microsoft.Data.SqlClient;
3
4var table = new DataTable();
5table.Columns.Add("Id", typeof(int));
6table.Columns.Add("Name", typeof(string));
7
8table.Rows.Add(1, "Ada");
9table.Rows.Add(2, "Grace");
10
11await using var connection = new SqlConnection(connectionString);
12await connection.OpenAsync();
13
14using var bulkCopy = new SqlBulkCopy(connection);
15bulkCopy.DestinationTableName = "dbo.People";
16bulkCopy.BatchSize = 2000;
17bulkCopy.NotifyAfter = 2000;
18
19bulkCopy.SqlRowsCopied += (_, e) =>
20{
21    Console.WriteLine($"Copied {e.RowsCopied} rows");
22};
23
24await bulkCopy.WriteToServerAsync(table);

This example sets BatchSize and NotifyAfter to the same value so progress logging aligns with each batch boundary. That makes benchmarking much easier.

What to Measure While Tuning

When you test batch sizes, measure more than elapsed time:

• transaction log growth
• lock duration and blocking
• CPU and memory on the SQL Server side
• client memory if the source is buffered
• retry cost when an error occurs near the end of a batch

The fastest raw runtime is not always the best production setting. A slightly smaller batch can be the better choice if it reduces blocking or makes failures cheaper to recover from.

Batch Size and Transactions

If you wrap SqlBulkCopy in a transaction, larger batches also mean larger rollback units. That can matter a lot on busy systems. A single failed import near the end of a huge batch may force SQL Server to undo a large amount of work, which is expensive and slows everything else down.

This is one reason staging-table imports often use different settings from direct imports into business-critical tables.

Common Pitfalls

• Looking for a magic batch size number ignores the fact that row width, indexes, and network conditions dominate the real answer.
• Setting BatchSize = 0 can be fine for small loads, but for large imports it may create an overly large unit of failure and logging.
• Testing only on a developer machine gives misleading results because production concurrency and I/O behavior are different.
• Using very large batches on indexed destination tables can increase lock duration and transaction log pressure enough to erase any throughput gains.
• Forgetting to benchmark with realistic data shape is a common mistake; one million narrow rows behave differently from one million wide rows.

Summary

• There is no universal recommended SqlBulkCopy batch size.
• Start around 1000 to 5000 rows, then benchmark under realistic conditions.
• Increase the size only if throughput improves without unacceptable blocking, logging, or rollback cost.
• Tune batch size together with transaction strategy, indexing, and destination-table purpose.