Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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




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


Learn more about SQL Server tools