Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How is a query against a linked server processed? Does the linked server use its optimizer when it receives a query from a remote source? There are a few scenarios about which I'm wondering:

Scenario 1:

A stored procedure/view relies entirely upon the linked server for a result set. Does the linked server process all of this and subsequently stream the result set back to the primary server via the network?

Scenario 2:

A stored procedure/view uses local and linked data. I'm assuming that the linked server returns a result set that the local server then executes against its own, local data. I'm also assuming that the local optimizer is not in play here because it has no knowledge of anything on the linked server until it's presented with a data set.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

I don't think this is empirically deterministic. My understanding is that an entire table could be brought over and processed on the calling server instead of being processed remotely on the linked server. If both servers are SQL Server 2008+ you should see scenarios like this:

SELECT x.foo FROM linked.db.dbo.x JOIN linked.db.dbo.y ON ...

And in these cases you will see that the join, filters etc. are processed on the remote server. But this should not be construed as a blanket statement! You may see slightly different remoting decisions in the plan for a slightly different scenario:

SELECT x.foo FROM localdb.dbo.x JOIN linked.db.dbo.y ON ...

These are for SQL Server 2008+ -> SQL Server 2008+ linked servers. Other linked servers (lower versions of SQL Server, other platforms altogether) may behave differently, and it may be version-dependent, it could even be specific driver-version dependent, or query-dependent (e.g. is a function used). And it of course could depend on the permissions of the linked server account - if you can't see remote statistics / cardinality for the distributed portion of the query, the local optimizer is going to be pretty blind and may not make the decision you want.

If you want to guarantee that processing occurs on the remote server before it starts streaming data across, and you have actually tried to get it work with four-part names and the plan does not try to process remotely, you can use OPENQUERY instead of explicitly referencing four-part names. Never tried that in a view, though. :-)

If you come across a case where you expect processing to occur remotely and it's not, I'm more than happy to look at the actual plan and the stats profile to help understand why (or at the very least to pass it on to some of my connections who can help).

From my own personal experience, I've found much, much, much more reliable and predictable performance by doing whatever I can to make as many queries as possible entirely local. Linked servers are useful but they certainly don't lend themselves to real-time performance. While you may think it violates DRY in some sense, I would rather pay that cost once during a mirroring session or replication and have redundant data, than potentially pay for a sub-optimal remoting decision for every single cross-server query. And if the data can be slightly delayed there are even more options for moving data across to make it available locally (log shipping, SSIS, custom).

share|improve this answer
    
Thanks. I should have known that things would change with different versions. I was hoping that there was some meta theory that would help me conceptually understand it better. –  swasheck Aug 8 '12 at 17:17
1  
Well, note that I said "may" - this is based on my own observations in our system at my previous job, where the same distributed queries were much happier on 2008 than on 2005. I don't really have any inside knowledge on what specifically changed, or how much of that change was due to the version. –  Aaron Bertrand Aug 8 '12 at 17:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.