SQL Linked Server Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

SQL Linked Server Performance

Author

Topic

aarondovetail
Starting Member

3 Posts

Posted - 2010-10-12 : 11:32:31

Hello all -- I'm pretty new to SQL Server outside of the general system admin stuff. I'm starting to get more into the SQL and database side at my new company, so I'll just say i'm a newbie.

Anyway, I'm trying to figure out why transferring data over a linked server is so slow compared to local on the same server. I know that the local server will be faster of course, but not that slow.

For example, I am running a query that does a select into with 22M rows that's about 6800MB of data. When I do the select from one DB into another DB on the same server it takes about 2 min 30 seconds. When I do the query to another server over a linked server it takes 16-20 minutes.

I'm watching the resource/activity monitor and it's only writing about 7MB/sec on the remote server. The network is full gigabit, both servers are new Win2008 R2 with SQL 2008 R2. When I copy a normal file across the network it can write at the full 120MB/sec.

It almost seems like there is something artificial holding it back, if you watch the network graph it has a perfect straight line at 7% of 1 gigabit used.

Query on local server:

Select *
Into ProspectsMain1
From VonagePreDatamart.dbo.ProspectsMain1
Where RecordID <= 1070000000
Go

Query to test linked server:

Select *
Into ProspectsMain1
From DB13.VonagePreDatamart.dbo.ProspectsMain1
Where RecordID <= 1070000000
Go

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-12 : 13:35:14

Linked servers are slow. You should move data in a different way.

Try the import/export wizard, SSIS, or bcp to move that much data between servers. I prefer bcp. You should be able to transfer millions of rows in under a minute.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

aarondovetail
Starting Member

3 Posts

Posted - 2010-10-12 : 13:40:31

I agree that the other methods will be faster, I actually did test the import/export wizard and it's faster. We have a lot of processes that use the link server method though, is there no way to speed up performance of that without re-inventing the wheel?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-12 : 13:43:01

I don't know of any config/switch that will improve your linked server speed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources