I have a issue with which I have had no luck in the KB, so I thought I'd ask it here to see if anyone has some ideas on how I can solve it:
We have 2 MSSQL servers both virtualized and roughtly the same spec (16 cores, 128 GB RAM and 10Gbit Network connection.
Betweween the servers we hav a databaselink set up using the OLEDB Provider for SQL server that comes with MSSQL - both also has the same version.
Also, the servers are both in our test enviroment and currently idle apart from my team using them. (4 developers)
My issue is that transfer speed when shifting data from one server to another is terribly slow:
For example I have a table with 500 Million records.
When I do the following it it takes about 6 hours
SELECT COL1, COL2 ... INTO T1 FROM DBLLINK.DB.DBO.SOURCETABLE
An insert into, select from takes days!
Now when I transfer the same amount of data using BCP its finished in less than 30 minutes (running 10 paralell BCP threads)
Also curious is that I have an SSAS server on another instance which reads this table and processes iit into a cube in under an hour.
both the performance from SSAS and BCP tells me there is nothng wrong with our network and that the problem must somehow lie with the dblink settings or the driver.
Its also clear to see that there is very limited network activity when using the dblink as opposed to SSAS or BCP.
We recemtöy upgraded from MSSQL 2017 to 2019 and I was hoping the updated drivers might help our problem, however it has made no difference.
In my experience DBLinks tend to be a bit faster than this but to be honest I lack the experience to say for sure and was wondering if anyone else have had similar experiences.
A final few notes
There is no difference whether I pull or push data and the database collation is the same on both servers.