SQL Server update query on linked server causing remote scan

Question

I have a SQL Server 2012 setup as a linked server on a SQL Server 2008 server.

The following queries executes in less than 1 second:

 SELECT kg.IdGarment
 FROM Products p 
 INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID 
 INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID 
 INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID 
 INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment 
 INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID 
 WHERE log.ActionType = 'I' 

t_ProcessIT_Garment contains 37,000 rows, the query returns two records, the IdGarment column is the Primary Key. No problem here.

However, if I run this query to do a remote update, it takes 24 seconds, and 2 rows is affected:

 UPDATE [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment SET 
 IdGarment = IdGarment
 FROM Products p 
 INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID 
 INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID 
 INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID 
 INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment 
 INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID 
 WHERE log.ActionType = 'I' ;

I tested using IdGarment = IdGarment to keep things simple. The execution plan shows it uses Remote Query for the first query, but Remote Scan for the second query, which has 100% of the cost.

The table joins are identical in both queries, why is it using Remote Scan for the second query, and how do I fix this?

Answer 1

A friend just had a similar problem and rewriting the query solved it.

I know this is an old question, and likely you have worked it out, but for anyone else you could try this and report back...

UPDATE kg
SET 
 IdGarment = IdGarment
 FROM Products p 
 INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID 
 INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID 
 INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID 
 INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment 
 INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID 
 WHERE log.ActionType = 'I' ;

I don't know why this helped, but using the UPDATE "alias" form of the statement meant that the whole UPDATE query was remoted, rather than pulling the data back to the local instance to perform the JOINs.

If anyone has any ideas why this behavior was seen, I would love to know.

Microsoft Docs has a good article about the subject (even if it's a bit old).

Answer 2

Do you have sufficient permissions on the table on the linked server? This behavior is documented in Books Online: Guidelines for Using Distributed Queries :

To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.

Answer 3

Adding the query hint OPTION (RECOMPILE) can encourage the optimizer to ship the entire query to the remote server. In my case, the query:

UPDATE [SERVER].[DB].dbo.RM00101
SET CRCARDID = 'SAGE',
 CRCRDNUM = @CRCRDNUM
WHERE CUSTNMBR = @CUSTNMBR;

plan showing a remote scan

took 75 seconds – with a primary clustered index on CUSTNMBR. Changing the query to the following brought the execution time below 50ms.

UPDATE [SERVER].[DB].dbo.RM00101
SET CRCARDID = 'SAGE',
 CRCRDNUM = @CRCRDNUM
WHERE CUSTNMBR = @CUSTNMBR
OPTION (RECOMPILE);

plan showing a single Remote Query node

Answer 4

If your remote scan operator is returning all rows, you may be experiencing the similar problem that occur when updating a table on linked server as described in this KB 942982. Although that is SQL 2005 issue, not sure if it applies to SQL 2008/2012. Try testing in your test environment by adding UNIQUE constraint or PRIMARY KEY to BarcodeRFID column in ZAMRentalGarments table and see if it helps.