SQL Server – Direct vs Linked Server Connection in Query Execution Time

linked-serverperformancequery-performancesql server

After investigating our ETL Process for months i fell into something strange.
We have one machine (A) which we are using in order to pull data from other machines (B).

  • When i make a SELECT Statement using Direct Connection from machine (A) to machine (B), the execution time is 09-10 secs
  • When i make the same SELECT Statement using Linked Server from machine (A) to the same machine (B), the execution time is 25-35 secs.

So why there is this difference?

The same data are transferred in each case.

enter image description here

Best Answer

In the left window, when you connect directly to FORBISPRD08:

  1. SQL Server Management Studio sends a request to
  2. FORNSQPRD02\NAV01, who has the data, and sends it back to
  3. SQL Server Management Studio

In the right window, when you use a linked server

  1. SQL Server Management Studio sends a request to
  2. FORBISPRD08\BIS01, who sends a request to
  3. FORNSQPRD02\NAV01, who has the data, and sends it back to
  4. FORBISPRD08\BIS01, who forwards it on to
  5. SQL Server Management Studio

See the difference? Same data might be sent, but there's more steps involved. When you need data, connect to the server that has the data.

Linked servers are like going to a bar, seeing someone you like, and then starting a chat with their friend instead. You might make progress - but it's not gonna be as fast.

Related Question