Performance Issues when Updating Data with a SQL Server Linked Server

By: Ranga Babu   |   Updated: 2016-08-26   |   Comments (1)   |   Related: > Linked Servers

Problem

SQL Server Linked Servers are a great to query remote databases, but when running update queries they don't always behave like they should.  In this tip I will show you a situation where several queries worked fine, but a few did not behave as planned.  I will also show you one way of handling this situation.

Solution

Let's say we have two SQL Server instances.  The two instances are SERV1 and SERV2.

I created this table with 90K rows of test data in database db1 on SERV1.

create table Update_Test (id int identity(1,1), val bit, name nvarchar(500))

declare @a int , @b int 
set @a=1 
set @b=900000

while @a<=@b
begin
   insert into Update_Test (val) values (1)
   set @a = @a+1
end

I also created a Linked Server named SERV1 on SERV2 that connects to SERV1.

Now we will execute some queries on SERV2 using the linked server.

SQL Server SELECT Query via a Linked Server

If I execute this SELECT query via the Linked Server it executes in less than a second. The execution plan is shown below. This sends the query to the remote server and executes the query there. (Note: I obfuscated some of the data in the screenshots for confidentially purposes.)

SQL Server DELETE Query via a Linked Server

Now if I execute a DELETE query via the Linked Server, it also executes in less than a second and the execution plan is below.

SQL Server UPDATE Query via a Linked Server

Now I will run an UPDATE on the nvarchar column this also executes in less than a second with the below query plan.

SQL Server UPDATE Query with Issues using the Linked Server

If I do an UPDATE on the bit column this takes 30 seconds to complete. The query is doing a remote scan using API Server cursor. You can see the query plan looks drastically different.

I had same problem with an UPDATE for a nvarchar(max) column.

Ways to Work around the Remote Scan Issue

To avoid the remote scan we created a stored procedure on SERV1 which accepts id and val as parameters.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Update_table (@id int, @val bit)
as
begin 
   SET NOCOUNT ON

   update Update_Test set val = @val where id = @id
end

Now when we call the procedure from SERV2 via the Linked Server it works without an issue.

exec [SERV1].db1.dbo.Update_table 2954,0

Next Steps

• When running queries using Linked Servers check the execution plans to see if there is a remote scan.
• As another workaround to solve this issue you can take a look at using OPENROWSET.
• Read more tips on SQL Server Performance Tuning.

About the author

Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips