Performance Issues when Updating Data with a SQL Server Linked Server
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 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
- 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
View all my tips