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: 
When you use linked servers for joins like this, it is important to have the server you are immediately connected to ("local") be the one with the most of the data, where the linked server is only providing a small part of the data, otherwise, yes, it will pull as much data as it needs to perform the join.

Alternatives include copying a subset of the data across to a temporary table with as much work done to slim down the results and any pre-processing that the linked server can perform, and then do the join on the "local" side.

You may find you can easily boost performance by reversing the way you do it, connecting to the server you have no control over (they'll need to make a linked server for you) and then connecting to your server over the link. If you need to do major work with the data where you would have to create sprocs - then push the data onto your server and use your sprocs there.

In some cases, I simply had the linked server perform a nightly creation of this kind of summary which it pushed to the local server, and then the local server performed its work with the join.
Its a very generous problem, which may have many solutions. But as we have witnessed so many user saying that they have tried everything.

What solved my problem is..

I upgraded sql server 2000 from sp2 to SP4 and if you already have sp4 on sql server 2000 then run Instcat.sql. As per my experience I can assure you this will work for sure, if you are exhausted with all the other workarounds.

Thanks,
Mithalesh
mithalesh.gupta@gmail.com