In case of a SQL JOIN on 2 databases across servers.

For example:

Select *
From server1.db1.dbo.tbl1 a
Join server2.db2.dbo.tbl2 b
On a.common_id=b.common_id
Where a.some_col = 1000

Suppose this query is run on server 2 via SSMS, then where does the query processing work? For example:

  1. Data for server1.db1.dbo.tbl1 needs fetching

  2. Then data for server2.db2.dbo.tbl2 needs fetching

  3. Then join to be performed.

  4. Then a filter

Where (on which server) does each of the above operation happen?

  • 1
    It depends on the execution plan. – Dan Guzman Feb 28 at 20:45

As Dan points out, it's dependent on the execution plan that gets generated, as the processing can occur on either end (the remote server or local server which executed the query). Usually you can determine which server it was actually processed on by seeing if there's a Remote Query vs a Remote Scan operation in your query's execution plan.

A Remote Query usually means the query is sent to the remote server to be processed, and the predicates will be performed remotely, resulting in less data being return back locally.

A Remote Scan generally results in all the data from the remote entities (e.g. the entire tables) to be returned to the local server first, then the query is processed, predicates are applied, etc, on the local server.

Evidently, Remote Query is usually a more efficient operator to have in your query's execution plan as opposed to a Remote Scan operator.

By the way, this is a great website with a quick description of the different query plan operators.

I've seen a lot of adhoc answers on query re-writes to try and fix plans with Remote Scan and force a Remote Query operation instead. I'm not positive on exactly what the SQL Engine is doing under the hood when it chooses one over the other, but I have consistently seen that when your query uses the primary key (and more likely resultantly the clustered index) field of the remote entities, as part of the predicates, it results in a Remote Query operation.

  • Is there predictable behaviour as to which transaction log will be used. Whether server 1 or server 2 database transaction log? Is the join and filter performed in memory or on disk? – variable Mar 1 at 7:58
  • See my reply for some elaboration. You'll probably realize that the answer is "no". it all depends on what query you end up with on each server. In general, SQL Server tries to push as much as possible to the linked server, but only what is actually executable on that linked server. – Tibor Karaszi Mar 1 at 13:41
  • @variable It depends on the query being ran, a pure SELECT statement creates no transactions to log and therefore neither transaction log is used. Otherwise whichever server the query is executed on is where the transaction log is used, for example in an UPDATE statement. The JOIN and WHERE clauses will be performed on the data in memory (when possible, data spills dependent) just the same as if you were running just a local query. – J.D. Mar 1 at 13:47

To expand a bit on Dan's and J.D.'s comments:

Note that SQL Server doesn't have a distributed optimizer.

In my early database days, some 3 decades ago, I came in contact with IBM's Distributed Relational Database Architecture, DRDA. This included, as I recall, a protocol for optimizers where they could share info and use a holistic view on the query execution.

Imagine you join two tables, one local (A) and one remote (B). You have a predicate on A making it return only three rows. The ideal here would be to ship the three rows from A to B, let B do the query to combine the data, and ship back the result to A.

SQL Server won't do this, since it doesn't have a distributed optimizer. SQL Server B isn't "aware" of SQL Server A. To B, server A is just a client app. Sure, B can expose meta-data about its tables and indexes and stuff to server A (to what extent depends on the capacity of the OLEDB provider used to talk to the remote DBMS), but that is not the same as a distributed optimizer.

So, what can SQL Server do? For above example, A can retrieve the three rows that satisfies the predicate, and do three remote queries. One for each row.

Also, note that a join doesn't necessarily mean that there has to be some temp data stored anywhere. Only one of the three join types does that (hash joins, the other join types being loop and merge).

Below is an example:

I have linked server named TK\B. I use Adventureworks, and have the same database on both A and B. There's an index on the local table for the WHERE clause, so only three rows satisfy that condition.

When the query uses a linked server, you see the same query being executed three times on the remote server.

Then I instead join to the same table but as a local table, you instead of those three query execution see one clustered index seek.

SELECT AVG(h.SalesPersonID),  AVG(UnitPriceDiscount)
FROM [TK\B].Adventureworks.Sales.SalesOrderHeader AS h
  INNER JOIN Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID
WHERE d.OrderQty = 34

SELECT AVG(h.SalesPersonID),  AVG(UnitPriceDiscount)
FROM Adventureworks.Sales.SalesOrderHeader AS h
  INNER JOIN Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID
WHERE d.OrderQty = 34

Here's the plan.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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