How can I prevent the SQL Server Query Optimiser splitting a remote query?

Question

I am using Microsoft SQL Server and I have a query which reads data from a remote server. This query reads data from 3 different tables across two different databases, all of which are on the same remote server.

When I run this query in isolation as a SELECT in SQL Server Management Studio, and inspect the execution plan - I can see that the entire query is being executed as a single remote query and the whole thing takes about 7 seconds to run.

If I attempt to INSERT the results of this SELECT into a temporary table, the execution plan completely changes. It runs 3 separate queries on the remote server, then merges the results locally - and takes nearly a minute to run.

Is there any way I can persuade the Query Optimiser to stick to the first, faster option!?

Edit: Thanks for the requests for the execution plans - here they are:

SELECT Only: https://www.brentozar.com/pastetheplan/?id=rygMoFHM8

INSERT (using the same SELECT): https://www.brentozar.com/pastetheplan/?id=S1EZhYBGI

Answer 1

Without seeing the query plans we can't answer in detail. There is likely a query hint you can use to encourage the query planner to use the alternate plan.

Another option is to control it from the other side: create a view in the remote end that does the work and select directly from that at the local side.

Update Once Query Plans Presented:

The warnings in the plan are often good clues, that is why they are there. In this case: Type conversion in expression (CONVERT_IMPLICIT(varchar(20),[NET2-1\NET2].[Net2].[dbo].[Users].[Field14_50],0)) may affect "Cardinality Estimate" in query plan choice.

It would appear that a difference in types between the source [NET2-1\NET2].Net2.dbo.Users.Field14_50 and the target #Net2Data.UserID is causing confusion. Without knowing the source type I suspect it is NVARCHAR(<something>). Perhaps you could add an explicit cast in the SELECT clause or change the temporary tables definition to match so there is no need for conversion at all. Also, if the source column in question is an NVARCHAR make the literal in the filtering clause explicitly a multi-byte string (LIKE N'[a-z][a-z][a-z]%' in place of LIKE '[a-z][a-z][a-z]%').

Answer 2

In the plans you posted we can see it's transferring more records (more network I/O), and it's also converting both the arguments you deliver to it, as well as converting the output upon inserting.

By the looks of it the remote DB has it's field as a N(VAR)CHAR instead of (VAR)CHAR. So when you're quering it using VARCHAR expression, it first has to convert these and also COLLATE them to the right character set.

2 of the 3 tables/sources hit are running somewhat fine. But one takes up nearly all the time:

<RunTimeCountersPerThread Thread="0" ActualRebinds="4787" ActualRewinds="0" 
ActualRows="2228" Batches="0" ActualEndOfScans="2559" ActualExecutions="4787" 
ActualExecutionMode="Row" ActualElapsedms="49195" ActualCPUms="48521" ActualScans="0" 
ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" 
ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0"/>
</RunTimeInformation>
<RemoteQuery RemoteSource="NET2-1\NET2" RemoteQuery="SELECT "Expr1018" FROM 
(SELECT "Col1036",MAX("Col1031") "Expr1018" FROM (SELECT 
"Tbl1017"."Address" "Col1024", "Tbl1017"."SubAddr" "Col1025", "Tbl1017"."Name""Col1026",
"Tbl1017"."PeripheralID" "Col1023","Tbl1017"."Type" "Col1027" FROM "net2"."dbo"."Peripherals" "Tbl1017" 
WHERE "Tbl1017"."Name" like N'%(In)%' COLLATE Latin1_General_CI_AS 
AND (NOT "Tbl1017"."Name" like N'%(Internal)%' COLLATE Latin1_General_CI_AS 
AND NOT "Tbl1017"."Name" like N'%Cats%' COLLATE Latin1_General_CI_AS 
AND NOT "Tbl1017"."Name" like N'%Dingle%' COLLATE Latin1_General_CI_AS OR "Tbl1017"."Name" like N'%(Internal)%' COLLATE Latin1_General_CI_AS 
AND ("Tbl1017"."Name" like N'%Cats%' COLLATE Latin1_General_CI_AS OR "Tbl1017"."Name" like N'%Dingle%' COLLATE Latin1_General_CI_AS))) Qry1028,
(SELECT "Tbl1015"."EventTime" "Col1031", "Tbl1015"."EventType" "Col1032", "Tbl1015"."Address" "Col1034", "Tbl1015"."SubAddr" "Col1035","Tbl1015"."UserID" "Col1036","Tbl1015"."CardNo" "Col1037", 
  "Tbl1015"."EventSubType" "Col1033","Tbl1015"."EventID" "Col1030" 
  FROM "Net2Events"."dbo"."Events" "Tbl1015" WHERE "Tbl1015"."EventType"=(20) 
  AND "Tbl1015"."EventTime"&lt;?) Qry1038 WHERE "Col1024"="Col1034" AND "Col1025"="Col1035" GROUP BY "Col1036") 
Qry1039 WHERE "Col1036"=?"/>

In the above query you can see a ton of like compares with collation. I suspect this is the slowdown. We can see it has a ~50 seconds execution time of which the most is CPU (which would suggest it's converting and comparing).

I would try to see if making both your temporary table use NVARCHARS as well as specifying your where arguments as N'' helps out. If not you can always try and use Openrowset and force execution entirely on remote side.

Basically in this case, you have two slow down factors. The 3 seperate executions (more data being transferred), as well as this last query from the execution plan which takes 50 seconds to execute on its own.

My apologies for the XML formatting, i already made it a lot less horrible than it is in the execution plan source.

Openrowset documentation