Linked Server Optimization

Question

Linked Server Optimization

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

DrewTheEngineer Say Hey Kid Points: 663 More actions · Answer 1

Good article ...

I use linked servers alot and have had performance issues as well, however my queries did not return a whole lot of data (they just took long to run). Even though my queries were optimized (i.e. they used index seeks when run directly on the remote server), they would produce table scans when I ran them as linked server queries. My solution was to write stored procedures on the remote servers and then call the stored procedures.

So, instead of doing this:

Select * from linkedserver.database.owner.table

I do this:

Exec linkedserver.database.owner.storedprocedure

Kindest Regards,

DrewTheEngineer

Bill Richards-377350 SSChasing Mays Points: 660 More actions · Answer 2

The response to this article has been great! I especially liked the ideas of remote joins, openquery , remote stored procedures, and table valued functions.

Almost every DBA shop needs to connect to remote servers and the feed back you have provided will be helpful to many people.

As some of you mentioned, replication would be ideal in this situation, and I agree. At a previous position, transactional replication to reporting and web servers was used to retrieve real-time information, balance the load on our primary servers, and avoid remote connections.

I will keep your responses archived, so that future remote connection projects can be optimized thoroughly.

Thanks,

Bill Richards, MCSE, MCDBA (Author of the Article)

Senior Database Analyst

mario-209768 SSC Rookie Points: 40 More actions · Answer 3

Hello all

It is possible to implement a federated database using linked-server? it'll be nice with msql2000 or mssql2005 could implement a strategic as its mentioned in this example (http://www.infoq.com/presentations/shoup-ebay-architectural-principles)

Best Regards

rposener Valued Member Points: 74 More actions · Answer 4

I wonder if anyone has any comments about the use of linked servers to connect different instances running on the same machine? Is the performance substantially better? I would think in this case it would be less critical, since there is no physical network IO?