Sorry for the long-winded question, I think its a pretty simple question I just want to make sure I'm asking it right:

We've discovered some fairly big problems with queries run against linked servers - for some reason any data selected from a linked server seems to select the ENTIRE table across the link, before
filtering on the WHERE clause. At least I think this is what's going on. But one thing is for sure, our queries against linked servers are much much slower than they should be, and I think its because
of the amount of unnecessary traffic coming acros the net work for even a small query. Anyway, regardless of the reason, linked servers are definitely causing performance issues for us.

However, in order to keep our stored procs consistant regardless of what database they are on, we want to use the linked server syntax even if the query is running agaisnt a local database.

For example:
Suppose I'm connected locally to [MyDatabase], and I have a linked server entry called [MyLinkedServer] that points to itself, ie 127.0.0.1

Then the following 2 queries are the same - they are returning the same results because they are selecting from the same database and table.

select top 10 * from [MyLinkedServer].[MyDatabase].dbo.MyTable
select top 10 * from [MyDatabase].dbo.MyTable

So my question is, does using the linked server version of the query have any performance impact at all? In other words, is SQL server smart enough to see that the linked server is localhost, and therefore just ignore the linked server request and end up actually just running the second query 'under the hood'?
Or does it go through the special linked server logic (whatever that is), ie downloading the entire table before applying the top 10 filter.

I have a feeling that unfortunately the 2 queries above are different, even if MyLinkedServer is actually the local server. I'm having a hard time testing and proving that however. So I was hoping to get an official answer if anyone knows.

We have 2 database servers with replicated data, and we have the same stored procedures installed on both. However we want both versions of the stored procedures to point to just one of the databases. We want the stored procedures to be exactly the same. The best way I can think to accomplish that is to have a linked server called MyLinkedServer on DB1 that points to DB2, and another linked server also called MyLinkedServer on DB2 that points to itself.
Then the stored procedures can be exactly the same on both databases, both referring to MyLinkedServer in any select statements.
The question is, will runing the queries on DB2 be as fast as they should be, or will they unnecessarily incur all the overhead that comes with using a linked server.

Thanks in advance,

When the data sources for a query are on the same server, that server can leverage indexes and statistics to come up with an efficient query plan.
When they are on different servers, you frequently end up performing table scans, or even transferring the entire table over the network in order to be processed locally.
You can limit the number of records scanned or returned by the remote server by using a WHERE clause to filter the remote dataset (the criteria will be applied by the remote server).
You can also speed things up by selecting only the necessary columns from the remote datasource. Do NOT use "Select *", as this will return every column over the network whether you need it or not.

Thanks very much blindman. It sounds like linked servers have some kind of performance improvement when they are actually a local linked server as opposed to remote ones.

I guess my real question is, are local linked server queries exactly as good as regular local queries, or is there still some kind of overhead imposed by the fact that you used the linked server syntax?

Off the top of my head, I don't know whether the query optimizer is smart enough to say "Hey!? These two datasources are on the same server! Well, I'll just ignore that fact that one of them is linked server and run the entire thing as a local query...."
Regardless, you'd still save bandwidth.

Ok fair enough. Thanks again blindman you always seem to be the first (and sometimes only) guy to answer my questions