Say Hey Kid
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:
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.
Bill Richards, MCSE, MCDBA (Author of the Article)
Senior Database Analyst
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)
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?
Viewing 4 posts - 16 through 18 (of 18 total)