question

Namtap avatar image
Namtap asked

DB link Performance Issue

Hi,

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.

Thanks

j

sql-serverssasnetwork
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
JohnM avatar image
JohnM answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Namtap avatar image
Namtap answered

Hi John,

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.

Thanks

Jacques

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·

What does the execution plan show? Without seeing the plan it's difficult to rule out the query.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.