I have two servers both on SQL Server 2017 that can get 500Mbps of network throughput between the two servers copying large files using Windows Share

Using SQL and a SELECT * INTO, I'm getting only 10Mbps

Any idea why using SSMS would be so much slower then the network capacity allows for?

closed as unclear what you're asking by hot2use, mustaccio, jadarnel27, Sean Gallardy, Paul White Feb 8 at 15:08

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    SELECT * INTO where? If it's a table on the same server you are measuring apples and unicorns, what does disk I/O on the server have to do with file copy performance across the network? What data are you sending over the network exactly? How are you measuring 10Mbps? If you are doing SELECT * INTO localtable FROM linkedserver...table (or SELECT * INTO linkedserver...table FROM localtable), you have to take into account the logging that has to happen on the write side. You're not just moving data, you're moving data in essentially an audited and flight recorded transaction. That's not free. – Aaron Bertrand Feb 8 at 2:59
  • 1
    There could be hundreds of causes. Help narrow down the possibilities for readers here by showing the table schemas and the query. Run sp_blitzfirst or do something to collect information during the process on both servers to see why it's not going as fast as you'd hoped. – Tony Hinkle Feb 8 at 3:00
  • @AaronBertrand - added more detail. Those speeds were referring to network IO. I did not look at physical IO their is more then enough IO capacity. Odd part is, with a 3rd server I get 200+Mbps using SQL linked server. – Manny Feb 8 at 4:21
  • 3
    Still not clear if you’re measuring Network I/O (and how) or if you are just dividing data size / duration. Again, writing rows of data to a table in SQL Server is very different from copying a file, and it doesn't really make sense to compare these or to expect equivalence. – Aaron Bertrand Feb 8 at 5:38