A Management Studio query pane connected directly to a remote sql server executes and displays a simple table query of (SELECT * FROM table) in just under 2 seconds.
The same Management Studio but now from a locally connected query pane, with the same query executed over a locally defined Linked Server (SQLNCLI10) now needs 7 seconds.
Comparing the client stats, while the number of server roundtrips is 1 in both cases, I do see that "client processing" in the first is just 1794ms, while over the linked server it is 5741ms. "Wait time on server replies" over the linked server is also higher 967ms vs 187ms.
This is SQL Server 2008 R2 64 at both ends and on the same domain.
Can anyone explain this?
Best Answer
A few quick ideas:
The local server initiates a connection to the remote server, runs a query and fetches appropriate rows from the remote server. This all takes time. Worse, if you trace such activity with SQL Profiler, you may find that rows are fetched from the remote server to the local server one at a time, or in very small batches. This is very inefficient, and the more rows it fetches the worse and worse it gets.
If all data is local, none of that happens and the data can be retrieved much more quickly, especially if it is sitting in the data cache.
Even though there may be indexing on tables on the remote server, SQL may not be able to take advantage of them while it can build a local query plan that does take advantages of indexing.
For very large remote result sets (in terms of data size), you may run into issues where SQL needs to grow data or log files to accommodate the data. 7 seconds probably doesn't qualify, since it probably isn't bringing back much data.
Lastly, if the remote query is to a production system and the local query is to a dev or test system, load on the production server (which does not exist on the dev server) may cause blocking of the remote query. This would also slow down the observed performance of the remote query.