2

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...

enter image description here

enter image description here

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...

enter image description here

Improve this question
3
  • 1
    I'd be curious to know if removing the COLLATE DATABASE_DEFAULT makes any difference.
    – Scott Hodgin - Retired
    Nov 1, 2016 at 15:06
  • The sub query is from SQL 2012 so I need the COLLATE DATABASE_DEFAULT for it to work. The main query is on 2008R2
    – K09
    Nov 4, 2016 at 13:08
  • Have you tried rewriting your Linked Table SELECT using OPENQUERY()? You can push the DISTINCT, JOIN and WHERE filtering to the remote server, instead of downloading all the table rows and filtering them out on your local server.
    – Oreo
    Apr 11, 2018 at 14:39

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

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

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