6

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?

Improve this question
2

1 Answer 1

Reset to default
3

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.

Improve this answer
3
  • I think you misunderstood the setting (or I didn't explain it well enough): BOTH situations query from the same SSMS client machine to the same remote destination server. Once with SSMS "directly connected" to the remote server, and next with SSMS connected to a local Sql Server instance which has a Linked Server object defined, which points to the same remote server as in the first.
    – Nick
    Commented Feb 22, 2012 at 19:09
  • I think that you are saying that it takes less time to run the query while directly attached to the server that holds the data and it takes more time to execute the query over a linked server connection. If there is no data on your local instance, everything still holds except for the stuff about joining local data to remote data. I think.
    – Darin Strait
    Commented Feb 22, 2012 at 20:29
  • 1
    Maybe another way to look at it is that SSMS is faster at retrieving a result set than SQL Server is, regardless of the source, plus the local SQL Server has to get the results from the remote server and then turn around and give them to SSMS.
    – Darin Strait
    Commented Feb 22, 2012 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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