Remote Query Cost - High Row Count I have a query that calls a remote table through Linked Server.
However, in the execution plan the Remote Query is 71% of total cost and the time taken to run is over 4mins. It returns all rows in the remote table (Actual Number of Rows value is over 1 million).

If I run the remote query separately, the Actual Number of Rows value is 2.

Why does it load all rows?

Below is the subquery part that calls the remote tables...





If I run the remote query part (the subquery) in a new window, the Actual Number of Rows is 2, and time taken is 1 second...
In versions of SQL Server earlier than 2012 (plus some patching), Linked Server permissions needed SA privs to use statistics information when processing remote queries. That could be what's messing you up.

For more information, see Conor Cunningham's talk on distributed queries here.