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)?

Thilde

Why don't you want to use dynamic SQL? I understand you don't want to concatenate strings making you vulnerable to SQL Injection but it is possible to do dynamic SQL with parameters. – Ijssel

Lots of reasons. SQL injection is just one. I also need to be able to support single-quotes in @stuffname, making it even more annoying to read because I have to escape those. The potential for syntax errors is increased because the string is not syntax-checked when the proc is altered. I find it unreadable. I want to be able to understand and control how these queries are run without being forced to use a literal. It's the only dynamic SQL in a large stored procedure (300 lines). And there's just the principle of it - this massive application has no other dynamic SQL in it anywhere. – Thilde

1. You do not have to and should not escape single-quotes in @stuffname when using parameters. 2. Yes syntax check, intellisense is not supported if you write the query directly in variable assignment part. If you do like in my answer you should write and test the query on server2 and then copy the query to the code on server1. 3. I have tested this from SQL Server 2014 to SQL Server 2008 and not experienced your problem. This could be something that is fixed since SQL Server 2005. 4. Principles are entirely up to you. There are many cases where dynamic SQL is the way to go. – Ijssel

It may be that dynamic SQL is only the way to for this task. I have already written an Dynamic SQL solution to this, and it's been working fine for the past few days. I'm here on Stack Overflow with this in hopes of finding a non-dynamic SQL solution. – Thilde

Did you do your solution with parameters or concatenation? – Ijssel

Have you tried converting stuffid to a uniqueidentifier? See my solution below. – Helterskelter

There is a good discussion on this over at #11281718 it seems the answer was to use exec sp_executesql – Subequatorial

Basically you are passing a parameter by reference and the query plan is generated sub-optimally because it's guessing wrong, there are several other interesting article linked from the article i linked above. – Subequatorial

It's quite a bit of reading. I am going to take a look. But if you can organize it into an answer to my question, then you can get the bounty. – Thilde

here is the explanation for "Why?" and a bit of suggestion on how to help it #314435 – Cherin

@MikaelEriksson I have just run 2 updates on a linked server, one with a variable and both generate the same query plan and run at the same speed (on my test - that updates one specific record) – Palm

@marcelomiorelli sorry I don't understand. Did you manage to recreate the bad plan op sees using linked servers? Did you try using sp_executesql in the remote server? Did you see the actual plan using sp_executesql? What did it look like? – Ijssel

@MikaelEriksson sorry I just wanted to make this problem work for this fellow. I had similar issues in the past and in my case it was due to not proper permissions settings in the linked server. so I went straight to the point. I can update tables on a sql2012 from a sql2005 and vice-versa. I showed how on the answer. – Palm

@marcelomiorelli I tried that too and it worked fine for me as well. I don't know why op sees what he sees but using sp_executesql on the remote server removes any issues the query has with remote execution since it is not executed remotely. Only the SQL string is passed as an argument to sp_executesql that in turn executes the query directly on the remote server. That solution is of course not possible if you need joins between local and remote tables but it does not look like that in this case. – Ijssel

@MikaelEriksson I have had lots of problems with dynamic sql. I am still having, as you can see here: dba.stackexchange.com/questions/104429/… – Palm

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

Ijssel

It is most likely not faster, because it uses a variable in the query as well. – Frissell

@StefanSteinegger Using a variable locally on server2 is not a problem. This will execute the stored procedure sp_executesql on server2 and send the parameter to the stored procedure giving SQL Server no chance to muck up the query plan like it currently does for OP. – Ijssel

It works for a single parameter. But I need it to work with 2 (the Id needs to be a parameter too) and that fails. Also, I already have a working dynamic SQL solution. – Thilde

@TheTTGGuy you can have more than one parameter to a query executed with sp_executesql. Just add more parameter declarations separated by comma and add the extra parameter values. You should not concatenate strings when parameters is an option. – Ijssel

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

Palm

Hi I think you have it backward. I have to insert into a SQL Server 2012 table from a SQL Server 2005 database. You are inserting into SQL Server 2005 from SQL Server 2012. – Thilde

As a result, I cannot copy all the settings you have. For example, Enable Promotion of Distributed Transactions is not available in the SQL Server 2005 version of the dialog box. – Thilde

@TheTTGGuy it works on the other way round as well, just ignore the Enable Promotion of Distributed Transactions. The most important thing is to have a login on the other side, with enough permissions to read and update statistics, then based on your stats it will generate the same query plan as you were locally. I will edit my answer to reflect this. – Palm

In that case, my settings already match for the linked server. – Thilde

@TheTTGGuy just pay attention to the security in the linked server, in my case is that sql login "monitor" with the permissions seen in the answer. It is all working here, both ways. sql2012-> sql2005 and sql2005 -> sql2012 – Palm

Answer 3

I would do it like this. Rather than sending the actual UPDATE query to execute to server2, I would make a stored procedure on server2 with the necessary parameters and call it from server1.

Inside the stored procedure you can tweak the query as needed using all capabilities of server2 to make it run fast (like OPTION(RECOMPILE), for example).

Besides, having explicit stored procedures like this defines an interface how two systems interact, which is good on its own.

Stair

I cannot make stored procedures on server2, unfortunately. This would probably work. The entire interface between the two databases must reside on server1. – Thilde

@TheTTGGuy, well, too much restrictions, good luck with your project. There is a good chance that sp_executesql will help. One more crazy idea: set up an intermediate server 3, which has same SQL Server version as your server 2 and create stored procedures (interface) on it. Server 1 (2005) calls stored procedure on server 3 (2012) with proper parameters; procedure performs remote UPDATE on server 2 (2012). As now versions of server 2 and 3 are the same (2012), there are better chances that you can use tweaks/hints/options to make the query run fast. – Stair

In the end, I tried this. Now I am getting distributed transaction errors. – Thilde

Answer 4

The solution to this should be that you ensure that the parameters you're using match the length and type of the columns. For instance, make sure that the NVARCHAR(64) column is targeted by a "DECLARE @var AS NVARCHAR(64)".

In your sample that seems to be the case, but when testing this in a local setup (with a SQLEXPRESS 2005 linked from a SQLEXPRESS 2014) I only get the "Remote Scan" when not matching up the length and type.

Mesotron

I would think so. It was one of the first things I checked. But it didn't fix the problem – Thilde

Answer 5

Perhaps the issue has to do with the unique identifier column. Have you tried: On server2 define the following stored procedure:

CREATE PROCEDURE updateStuff( @newstuff nvarchar(30), @stuffid    varchar(36))
AS 
 UPDATE Stuff
 SET stuffname=@newstuff
 WHERE stuffId=convert(uniqueidentifier, @stuffid))

The from server 1 invoke: exec server2.database2.updatestuff N'New stuff', '4893CD93-08B3-4981-851B-5DC972288290'

OLD suggestion: Declare @stuffid uniqueidentifier Set @stuffid = convert(uniqueidentifier,='4893CD93-08B3-4981-851B-5DC972288290') UPDATE [server2].database2.dbo.Stuff SET stuffname=@newstuff WHERE stuffId=@stuffid

Helterskelter

@TheTTGGuy Do you have an index defined on stuffId? – Helterskelter

yes. There is a clustered index on the primary key. – Thilde

@TheTTGGuy Just realized you said that you are updating this from SQL Server 2005? But that does not support uniqueidentifiers. So how can you have implemented the above on that server? I think the issue has to do with the fact the sql server 2005 does not support uniqueidentifers. If you define a stored procedure on server 2 and invoke that stored procedure on server 1, then I think it will work – Helterskelter

You are mistaken. We have always have uniqueidentifier fields in our sql server 2005 implementation – Thilde

@TheTTGGuy Are you able to define the above procedure on the SQL Server2012 implementation? When you run it there does it run quickly and use the index? One other question. Does stuffId have the ROWGUIDCOL property set? – Helterskelter

I am not able to put the procedure in the SQL Server 2012 implementation. ROWGUIDCOL is not set. – Thilde

@TheTTGGuy Can you run the OLD suggestion directly in SQL Server 2012 just to see what it does? To me the issue has to do with stuffid. The server should use the index on that field. Not clear on why you cannot add a stored procedure. It cannot affect anything else. – Helterskelter

Answer 6

You can try setting the value right in the variable declaration:

DECLARE @newstuff nvarchar(64) = 'new stuff'

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

I think deleting the select part would help.

Renelle