Question: At work we have two servers, one is running an application a lot of people use which has an SQL Server 2000 back end. I have been free to query this for a long time but can't add anything to it such as stored procedures or extra tables. 

This has lead to us having a second SQL Server linked to the first one and me building up a library of stored procedures that query data from both sides using linked server. Some of these queries are taking longer than what I would like. 

Can someone point me to some good articles about using linked servers? I am particularly interested in finding out what data is being transferred between the two as usually the majority of the sql statement could be performed remotely but I have the feeling it may be transferring the full tables, it is usually just a join to a small final table locally.

Also what do the linked server options do I currently have:

* Collation Compatible True
* Data Access True
* Rpc True
* Rpc Out True
* Use Remote Collation False
* Collation Name (Blank)
* Connection Timeout 0
* Query Timeout 0

**EDIT:**

Just thought I would update this post I used openqueries with dynamic parameters for a while to boost performance, thanks for the tip. However doing this can make queries more messy as you end up dealing with strings. finally this summer we upgraded SQL Server to 2008 and implemented live data mirroring. To be honest the open queries were approaching the speed of local queries for my tasks but the mirroring has certainly made the sql easier to deal with.

Answer: 
I wrote a remote Linked Server application in SQL 2000 a couple of years ago and came across the same performance issues you describe. I ended up rewriting my stored procedures several times in order to obtain the best performance.

I used temporary tables extensively. I found that it was less expensive to retrieve large amounts of remote data into a temp table, then join to it, manipulate it, etc. Joining local to remote tables was very slow as you desribe.

Display Execution Plan and Display Estimated Execution Plan tended to help although I did not understand a lot of what I was looking at.

I don't know if there really is a efficient way to do these queries with a remote server because it seems like SQL Server cannot take advantage of its normal optimizations when going against a Linked Server. It may feel like you are transferring the entire table because in fact that is what is happening.

I am wondering if a replication scenario might work for you. By having the data on your local server, you should be able to write normal queries that will perform as desired.

I do not know of any good articles to point you towards. As I write more complicated SQL Server applications, I started to think that I needed a better understanding of how SQL Server worked underneath. To that end we bought the MS Press Inside Microsoft SQL Server 2005 series edited by Kalen Delaney here at work. Volume 1: The Storage Engine is definitely the place to start but I have not gotten that far into it. Since my last few projects have not involved SQL Server, my study of it has gotten lax.
Dynamic SQL and a function can be used to get around the hard-coded name question. For instance, I'm trying an implementation where function ufn\_linkedDatabase(@purpose nvarchar(255)) with input 'cpi.cpi' (purpose CPI, sub-purpose default) returns 
'[SERVER-NAME.DOMAIN.LCL,2000].[CPI]' in the production environment (where we use alternate port number for SQL Server, I don't know why, including in linked server name). Then a SQL command is assembled in @template varchar(max) with the expression @{cpi.cpi} representing the linked server and database, and then 
@workstring = REPLACE(@template, N'@{cpi.cpi}', ...) . How the function actually gets the database name is separate from the procedures - a lookup table is nice. 

Issues - to do OPENQUERY(), which is probably still better at least unless the linked server option "collation compatible" is set "true" so that more of the task can be executed on the linked server - important even on a fast network, and our server room internal network is respectably fast - to do OPENQUERY() I probably need to handle 'cpi.cpi.server' and 'cpi.cpi.database' and 'cpi.cpi.server.database' separately. And, I may end up writing exactly one application using this design, in which case it's over-designed. Still, that means that the function itself doesn't have to be any kind of fancy work.

Throwing fast network hardware at the problem may be the cheaper answer, anyway.