Direct vs Linked Server Connection in Query Execution Time

by Stavros Koureas   Last Updated January 11, 2018 13:06 PM

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



Answers 1


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.

Brent Ozar
Brent Ozar
January 11, 2018 12:21 PM

Related Questions


TVF Poor Performance with Linked Query

Updated December 28, 2016 08:02 AM