How do I increase SQL Server read speed?

Question

I'm trying to copy a table from one SQL Server to another. Source is SQL Server 2014, destination is SQL Server 2016. I cannot get speeds faster than about 60MB/s. I have about 1.5 billion rows to copy, total table size of roughly 1TB.

The source server has a RAID 5 of SATA SSDs, the destination has a raid 0 of NVME SSDs. Both machines are connected by 10GbE, direct with no switch. I am able to copy a few GB of files in a few seconds, so I think the network and disks are all configured properly.

I'm using SSIS, and I've tried just a "table" source, and a select * query. The package creates the destination table, and I'm using fast load. I've tried oledb for SQL Server, and SQL native client for the connection types. I have tried anywhere from 1 to 12 parallel source/destinations in my data flow, and I get about 3x the speed going from 1 to 12. But I don't have an easy way to divide the data further to increase parallelism.

I've played with buffers, and packet size to no avail.

The source table has about 100 columns.

I was expecting to at least get a 300-500MB/s for this, and hopefully at least saturate the source disk read speed (~800MB/s). Am I missing something or is this just a limitation of SQL Server?

Answer 1

I don't have a specific answer to this question, but i will try to provide some information that may give some insights:

Selecting specific columns

First of all, if there are some columns in the OLE DB Source that wont be used change the Access Mode and use SQL Command instead of Table or View and specify the columns needed in the Select query. As example, if the table contains 5 columns [Col1],[Col2], ... [Col5] and you only need [Col1],[Col2] use the following query:

Select [Col1],[Col2] From [Table]

Instead of of selecting the Table name

For more information:

• SSIS OLE DB Source Editor Data Access Mode: “SQL command” vs “Table or view”

Minimize logging

There are one method you can try which is minimizing the logging by using clustered index. check the link below for more information:

• New update on minimal logging for SQL Server 2008

Balanced Data Distributor

Also you can benefit from Balanced Data Distribution Transformation to enhance data loading performance:

• Parallelize Data Flows with SSIS Balanced Data Distributor Transformation
• Balanced Data Distributor: Improve SSIS Performance with Parallelism

Helpful articles

In 2008 Microsoft published a white paper to announce a record-breaking data load using Microsoft SQL Server Integration Services (SSIS): 1 TB of data in less than 30 minutes. It may give you some insight:

• Interesting White paper – We Loaded 1TB in 30 Minutes with SSIS, and So Can You

I think there are many way to enhance the performance on the INSERT query, I suggest reading the following article to get more information about data loading performance.

• The Data Loading Performance Guide

---

Also check @billinkc comments concerning the Max Insert Commit Size property, data types, bulk insert ...