SQL Server Performance Tuning

How to Copy Large Volumes of Data from One Table to Another Database[Or 18Gb in 5min]

Updated November 11, 20264 min read

Copying 60 million rows fast: Ploom driving a freight train of boxes

Today’s SQL challenge: Copying an 18.2 GB table (60 million rows) from one SQL Server to several others.

When reviewing data with T-SQL for Machine Learning exploration, you discover that one of the fields is a VARBINARY(max). During testing, table creation alone took over 11 minutes.

Another option is using SSIS with a Balanced Data Distributor, but it requires transferring 18.2 GB across multiple networks, creating heavy network load.

Preferred approach:

  • Use BCP in parallel to export data into multiple files.
  • Compress each file in parallel.
  • Transfer compressed files to destination servers.
  • On each destination, use BCP in parallel to import into separate tables.
  • If consolidation is needed, use partitioned tables or views.

My Method

PowerShell commands used for extraction and compression:

PowerShell
Start-Process PowerShell -ArgumentList {
    New-Item -ItemType Directory -Path D:\Backup\Backup1\;
    
    bcp "SELECT * FROM Database.dbo.Table WHERE Id BETWEEN 1 AND 3750000" `
    queryout D:\Backup\Backup1\Backup1.out -S. -T -N -a 65535;
    
    Add-Type -AssemblyName System.IO.Compression.FileSystem;
    
    [IO.Compression.ZipFile]::CreateFromDirectory('D:\Backup\Backup1', 'D:\Backup\Backup1.zip');
}

Running these commands in parallel lets you launch multiple tasks simultaneously, completing the entire process in about 4 minutes.

In this setup, the SQL Server instance has 20 cores, with MAXDOP set to 8.

No noticeable performance difference was observed between using 8 vs. 20 threads. Total runtime remained nearly identical.

However, running a single thread can backfire. While SQL throughput spiked to 265K rows/sec, the zipping phase slowed to 6 minutes, pushing total time to 11 minutes.

After compression, all ZIP files totaled just 750 MB. On a 100 GbE network, transfer to each target server is nearly instant. Offline transfer is also an option.

Once the data is in place, unzip in parallel. It takes just a few seconds. Even serially, it finished in 99 seconds.

Next, load data into individual tables using PowerShell and BCP (example, single line):

PowerShell
Start-Process PowerShell -ArgumentList {
    bcp Database.dbo.Backup1 in D:\Backup\Backup1.out `
    -S. -T -N -a 65535 -h "TABLOCK" -b 10000
}

All loads completed in 61 seconds. Attempting this directly into a single table causes contention and takes about 30 minutes instead.

Total end-to-end time: 5.3 minutes.

Final note: you’ll have multiple tables. To consolidate, you can create a VIEW or copy the rows. But the fastest method is using partition functions and schemes to logically merge them - takes virtually no time.

Ideas for additional speed improvements:

  • If this operation will be repeated, consider partitioning the source table. A well-designed partitioning scheme lets you transfer only new or changed data, not the entire table. Partitions can be moved to separate tables before export, improving performance.
  • Use multiple drives for output files if possible. I had only one spare drive besides the DB drive, but using locally mounted SSDs boosts throughput.
  • Enable jumbo network packets to increase data transfer rates between storage and the DB server.
  • If the table is in-memory, data doesn’t need to travel through storage or the network, which speeds things up.
  • Pre-grow the destination database before loading. This reduces delays during import.
  • More tips available in Microsoft’s article.

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜