4

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Matthew Dixon
  • 89
  • 2
  • 5
  • 2
    In your OLE DB Destination, try setting a reasonable size for the Maximum Insert Commit Size. Also, ensure your target server is ready to receive that much data (presize your logs, ensure you have instant file initialization set, etc). For a one-time task, you could also look at BCP out, copy over the network, and BCP back in – billinkc May 09 '19 at 22:47
  • 2
    Other things to check is what are the data types involved? If you have max/lob types then your performance will be bad. If the receiving table is empty, ensure there is at most 1 index on the target table that being the clustered index (if applicable) – billinkc May 09 '19 at 23:03
  • 2
    Identify bottlenecks, check if IO on both servers are similar. You didn't mention if target database is running in simple/bulk backup mode. Check if all requirements for minimal logging are meet. – Piotr Palka May 09 '19 at 23:03
  • Is the issue solved? have you tried the suggestions provided (answer + comments)? – Yahfoufi Jun 13 '19 at 12:03
  • Aside from speed, am I missing something about going from RAID-5 with redundancy in case fail drive to a RAID-0, drive fails, your done for? – DRapp Feb 16 '21 at 22:52
  • @DRapp I'm going from OLTP system of record to a database optimized for ad-hoc analysis from many external technologies – Matthew Dixon Feb 17 '21 at 23:10

3 Answers3

0

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:

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:

Balanced Data Distributor

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

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:

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.

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

It has been a long time since I asked this question.
Thank you to all that contributed.

I tried all of the suggestions with mixed results. I didn't want to go to a process where I was outputting text files then re-importing them, and I needed to keep all of the columns in the table.

In the end the only thing that got me to "as fast as can be" was to create the destination table with no indexes, run 32 parallel read/write data flows, then create the necessary index after.

I could never saturate the 10Gb connection, but all 32 threads of the CPU on the destination machine (Threadripper 2950X) were maxed out at 100%. This is probably due to the software NVME RAID.

Using this method and playing around with the insert commit sizes eventually got me to about 600-700MB/s. Adding the clustered index was fairly fast after that, just a few minutes.

Hopefully this helps for anyone else trying to do the same thing.

Matthew Dixon
  • 89
  • 2
  • 5
0

Aside from OleDB and Native Client, have you tried writing a stored procedure so that SQL is doing it at the engine directly between devices. We dont know what your actual process does, nor how your creation of the final table is prepared. What are the processes involved in your "package" creation. You mentioned a sample "select * from SomeTable" and not getting any performance.

If you have 10-12 processes trying to read and write to the same table being created, are you not cutting your own throat so-to-speak? Also, how many indexes are on your destination table. It MIGHT be performance beneficial to delete all indexes except maybe your a primary key ID only. If you are using a CLUSTERED index on your table, that is constantly going to be sorting the data as it writes (to best of my understanding). Also, you'll be dealing with collisions having to wait while one process finishes its insert and the next has a chance to go.

If you did something like

select 
      YT.*
   into
      YourNewDatabase.dbo.YourNewTable
   from
      YourPrimaryDatabase.dbo.YourMainTable

The SQL engine will have a single stream of activity reading all the raw data pages, which if clustered will be written out in same already pre-sorted clustered on the new drive without the explicit clustered index. You also don't have anything coming back to your local machine for processing by the "package". Once the data is completely on the other drive, then you could build ALL your indexes once vs individually creating one after the other after the other.

I don't know how SQL-Server vs MySQL do their "create index" behind the scenes, but many years ago, I was working on a data-mining system. The process had its table of 21+ million records and for the system had about 20 indexes based on how the queries were available. If I did a create index one at a time, then another, then another, it was like it compounded the time. 1st index = 20 minutes. 2nd index = 40 minutes, 3rd = 1hr, etc. But if I did a single create index for 1, 2 and 3, the entire index creation was about 35 minutes. Must be how the indexes and pages get written. So, you may need to play around with that for consideration too.

So, for a direct copy from device A to B, I would try that. Do with / without indexes, try that. If you are pulling the data out of SQL1 pulling locally to process, then pushing back out to SQL2 for a BILLION+ records, it obviously will take time.

DRapp
  • 47,638
  • 12
  • 72
  • 142