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.
Answer by JohnM ·
Have you looked at the execution plan for the query? Usually linked servers don't perform overly well depending on how the query is written. As you've mentioned, BCP with 10 parallel threads still took 30 minutes whereas a linked server query is going to be single threaded in that sense.
My initial guess is that it's not the network that is bottle neck but rather how the query is written and/or the database engine itself.
Answer by Namtap ·
Thanks for your answer. The query is a straight select off a table. Pretty straight forward. If I do the select into on the local instance it runs in under 5 minutes, so I doubt that its a optimized issue. Also as said our SSAS server can read the data from over the network in well under an hour. The problem seems to specifically to do with query results going over the dblink.
SSAS Tabular OLE DB or ODBC error : The Microsoft Access database engine cannot open or write to the file. It is already opened exclusively by another user, or you need permission to view and write it's data.; 3051.