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

  • 1
    I'd be curious to know if removing the COLLATE DATABASE_DEFAULT makes any difference. – Scott Hodgin Nov 1 '16 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 '16 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 '18 at 14:39
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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