Performance Issues when Updating Data with a SQL Server Linked Server

By:   |   Comments (1)   |   Related: > Linked Servers


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.


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
   insert into Update_Test (val) values (1)
   set @a = @a+1

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 Execution plan with select statement

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 Execution plan with delete statement

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 Execution plan of a update on nvarchar column

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.

SQL Server Execution plan of a update on bit column

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.


CREATE PROCEDURE Update_table (@id int, @val bit)

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

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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ranga Babu 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

Comments For This Article

Wednesday, November 28, 2018 - 8:33:04 PM - Ignacio Jiménez Back To Top (78352)


I have read your interisting post. I have a doubt regarding remote scan I woul very thanke to you if you could help me with it. When a remote scan is performed, it means that:

a) Is the scan carried out in the remote server and the matching  rows sent to the local server; or

b) first all rows are sent to the local server and then the scan is carried out in the local server?

 Thanks in advance,


get free sql tips
agree to terms