Remote Query is slow when using variables vs literal

Question

I've searched everywhere for this situation and can't find a solution except for Dynamic SQL, which I don't want to use.

Here is the table I want to UPDATE on server 2:

(Stuff Id UNIQUEIDENTIFIER
, stuffname NVARCHAR(64))

I need to update it from server 1.

So I have been attempting this:

DECLARE @newstuff nvarchar(64)

SELECT @newstuff = 'new stuff'

UPDATE [server2].database2.dbo.Stuff
SET stuffname=@newstuff
WHERE stuffId='4893CD93-08B3-4981-851B-5DC972288290'

That takes 11 seconds. This next one using a literal runs in under 1 second

UPDATE [server2].database2.dbo.Stuff
SET stuffname='new stuff'
WHERE stuffId='4893CD93-08B3-4981-851B-5DC972288290'

I have compared the actual execution plans. The slow one is doing a Remote scan that takes 100% of the cost, plus 5 other steps (filter, table spool, compute scalar, remote update, update). The fast one just does the UPDATE and Remote Query steps. I need to use variables, so I need a way to force it to do the whole query remotely.

I have tried using OPTION(RECOMPILE) but server1 is using SQL Server 2005. server2 is using SQL Server 2012. I can't change the database structure at all on server2 without serious problems. I am not having any authentication problems. I have tried aliasing the table when updating it.

I have also tried using Openquery. When I put the id filter within the query string, it gets back down to under 1 second:

UPDATE OPENQUERY([server2], 'select stuffname, stuffid from database2.dbo.stufftable where contactid=''4CA1D489-9221-E511-A441-005056C00008''')
SET stuffname = @newstuff

But I need that id to be a variable as well, and that open query does not take variables (https://msdn.microsoft.com/en-CA/library/ms188427.aspx). I tried running Openquery with the id filter outside the query, but that runs in 4 seconds. It's better than 11, but not great:

UPDATE OPENQUERY([server2],'select stuffname, stuffid from database2.dbo.stufftable')
set stuffname=@newstuff
where contactid='4CA1D489-9221-E511-A441-005056C00008'

Of course, I run openquery using exec(@sql), but I really don't want to go that way. I could do the whole update statement that way using literals and not even use OPENQUERY and get the same sort of result anyway.

Is there any way for me to get this performance fixed without using exec(@sql)?

Answer 1

You can use dynamic SQL with parameters using sp_executesql on the remote side.

declare @SQL nvarchar(max);

set @SQL = 'UPDATE database2.dbo.Stuff
            SET stuffname=@newstuff
            WHERE stuffId=''4893CD93-08B3-4981-851B-5DC972288290'''

exec [server2].master.dbo.sp_executesql @SQL, N'@newstuff nvarchar(64)', @newstuff

Answer 2

I believe your problem is related to the permissions that you are running the connection to the LINKED server with.

there are links where this case has been explained and I have had similar experiences. here are a couple of links:

OPENQUERY when executing linked server queries in SQL Server

TOP 3 PERFORMANCE KILLERS FOR LINKED SERVER QUERIES

I will post my solution below.

I have set up an environment to test your solution. my server2 is sql server 2005

my server1 is sql server 2012.

On server2 I have created and populated the stuff table in the following way:

I use a database called tablebackups with a specific naming convention but I am sure you can understand: The result is a table with a clustered primary key on a identity field and another field for the update. this table in my example has 100,000 records.

select @@version
--Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) 
    --Dec 10 2010 10:56:29 
    --Copyright (c) 1988-2005 Microsoft Corporation
    --Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

use tablebackups
go

CREATE TABLE dbo._MM_201504710_stuff ( Id UNIQUEIDENTIFIER
                    , stuffname NVARCHAR(64)
                    )

ALTER TABLE dbo._MM_201504710_stuff ADD  CONSTRAINT [PK_Stuff] UNIQUE CLUSTERED ( ID );

-- add 100,000 records to the table so that we can have an idea of execution
SET NOCOUNT ON
insert into dbo._MM_201504710_stuff values (NewID(),'Radhe Radhe')
GO 100000 -- 100,000
SET NOCOUNT OFF
--this took 19:38


--just to test
SELECT TOP 100 * FROM dbo._MM_201504710_stuff
--18D4BDEA-6226-47E1-94DB-00402A29798F



DECLARE @newstuff nvarchar(64)
SELECT @newstuff = 'new stuff'
UPDATE dbo._MM_201504710_stuff
SET stuffname=@newstuff
WHERE Id='18D4BDEA-6226-47E1-94DB-00402A29798F'



UPDATE dbo._MM_201504710_stuff
SET stuffname='new stuff'
WHERE Id='18D4BDEA-6226-47E1-94DB-00402A29798F'

the execution plans of these updates are very similar and are not an issue. As you can see on the picture below.

Before going to server1 I double check when the statistics of my stuff table have been updated, because this will influence the query plan generation. Just to be sure.

Then I go to server1.

NO, Before I go to server1, on server2 I have this sql login with the following permissions:

I call it "monitor"

and for the permissions of "monitor" I use this select:

SELECT p.[name], sp.permission_name, p.type_desc AS loginType FROM sys.server_principals p 
  JOIN sys.server_permissions Sp
   ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100 

That shows me these permissions:

NOW on server1 I have a linked server to server2 (sqlsalon1.dev.boden.local) and this LINKED server use "monitor" to connect to server2.

as you could see above, this monitor sql user has all the required permissions to see and update the statistics and therefore we can use the best plan even when running remote transactions.

ON SERVER1: I connect to server2 using the following linked server:

running these scripts (less than a sec)

-- just to test
select top 100 *
from [SQLSALON1.dev.boden.local].tablebackups.dbo._MM_201504710_stuff


--first update
UPDATE  [SQLSALON1.dev.boden.local].tablebackups.dbo._MM_201504710_stuff
SET stuffname='new stuff'
WHERE Id='18D4BDEA-6226-47E1-94DB-00402A29798F'



--second update
DECLARE @newstuff nvarchar(64)
SELECT @newstuff = 'new stuff'
UPDATE [SQLSALON1.dev.boden.local].tablebackups.dbo._MM_201504710_stuff
SET stuffname=@newstuff
WHERE Id='18D4BDEA-6226-47E1-94DB-00402A29798F'

I get this query plan:

So, double check the permissions on the linked server account, if you copy mine I believe your problem will be solved, since this is working here, unless there is something else different, and in that case, please let me know, I will try to tackle it further.

The other way round From SQL 2005 Updating a table in SQL 2012

on sql 2012 create and populate the table

select @@version
--Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--  May 14 2014 18:34:29 
--  Copyright (c) Microsoft Corporation
--  Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


use tablebackups
go

CREATE TABLE dbo._MM_201504710_stuff ( Id UNIQUEIDENTIFIER
                    , stuffname NVARCHAR(64)
                    )

ALTER TABLE dbo._MM_201504710_stuff ADD  CONSTRAINT [PK_Stuff] UNIQUE CLUSTERED ( ID );

-- add 100,000 records to the table so that we can have an idea of execution
SET NOCOUNT ON
insert into dbo._MM_201504710_stuff values (NewID(),'Radhe Radhe')
GO 100000 -- 100,000
SET NOCOUNT OFF
--this took 19:38


--just to test
SELECT TOP 100 * FROM dbo._MM_201504710_stuff
--3E29A8E5-BA57-4A9C-803E-003C13A80905

after the table is populated I check for the statistics

it turns out that the statistics were NOT updated

so I update the stats:

--================================================
-- HAD TO UPDATE THE STATS BEFORE RUNNING THE UPDATES
--================================================
UPDATE STATISTICS dbo._MM_201504710_stuff

I check again and it is fine this time.

Create the linked server from sql 2005 to sql 2012:

USE [master]
GO

/****** Object:  LinkedServer [SQLMON1]    Script Date: 13/07/2015 17:09:08 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SQLMON1', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQLMON1',@useself=N'False',@locallogin=NULL,@rmtuser=N'monitor',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'use remote collation', @optvalue=N'true'
GO

I have removed the below server option.

Check the permissions of "monitor" on the target server

SELECT p.[name] collate database_default, 
       sp.permission_name, 
       p.type_desc AS loginType 
   FROM sys.server_principals p 
  JOIN sys.server_permissions Sp
   ON p.principal_id = sp.grantee_principal_id
    WHERE sp.class = 100 
    and name = 'monitor'

and after that we can run the updates from the sql 2005 server.

--first update
UPDATE  [SQLMON1].tablebackups.dbo._MM_201504710_stuff
SET stuffname='new stuff'
WHERE Id='3E29A8E5-BA57-4A9C-803E-003C13A80905'



--second update
DECLARE @newstuff nvarchar(64)
SELECT @newstuff = 'new stuff'
UPDATE [SQLMON1].tablebackups.dbo._MM_201504710_stuff
SET stuffname=@newstuff
WHERE Id='3E29A8E5-BA57-4A9C-803E-003C13A80905'

and this will update the row, with or without a variable, in the same way. quick as a bolt