Performance Issues when Updating Data with a SQL Server Linked Server


By:   |   Updated: 2016-08-26   |   Comments (1)   |   Related: More > 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<[email protected]
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 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.

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.


Last Updated: 2016-08-26


get scripts

next tip button



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

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

Hello,

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,

Ignacio



download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools