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?