0

I have a query that includes accessing a table via linked server. When it runs it's doing so without parallelism. But if I comment out the join to the linked server table it runs with parallelism and completes almost instantly.

The row count of the table accessed by the linked server is 2m.

Could the linked server be causing the query to run without parallelism?

Here is the plan.

  • The easiest way to start troubleshooting this is to add OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) to the query that isn't going parallel. It could be a cost based decision. It's not included in the XML you provided, but there may be a NonParallelPlanReason in the XML if there's an optimizer limitation involved. – Erik Darling May 24 '18 at 12:55
3

Joining to a table via a Linked Server is problematic. You can workaround the issue by pulling the required rows from the remote table into a local #temp table, and JOINing against that #temp table instead.

  • Nice Comment, Max ;^} – Erik Darling May 24 '18 at 12:52
  • 2
    lol, it does attempt to provide about as much answer as the question allows, I guess. – Max Vernon May 24 '18 at 12:53

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.