2

I'm issuing a fairly simple update of single varchar column against a remote linked server. E.g.:

UPDATE Hydrogen.CRM.dbo.Customers
SET EyeColor = 'Blue'
WHERE CustomerID = 619

And that works fine when is written as an ad-hoc query:

enter image description here

Parameterized Queries Bad

When we do what we're supposed to do, and have our SqlCommand issue it as a parameterized-query, the SQL ends up being:

exec sp_executesql N'UPDATE [Hydrogen].[CRM].[dbo].[Customers] 
SET [EyeColor] = @P1 
WHERE [CustomerID] = @P5', 
N'@P1 varchar(4),@P5 bigint',
'Blue',619

The query ends up performing a Remote Scan against the linked server:

enter image description here

It creates a cursor on the linked server, and takes about 35 seconds to pull back 1.2M rows to the local server through a series of hundreds of sp_cursorfetch

Why, in the world, would the local SQL Server optimizer ever decide to pull back all 1.2M rows to the local server in order to update anything?

It only fails on varchar columns

If i update int fields, it works fine.

But this column is varchar - and it fails.

I tried other parameterizing the column as nvarchar, and it's still bad.

Every answer i've seen actually are questions:

  • "is the collation the same?"
  • "What if you change the column type?"
  • "Have you tried OPENQUERY?"
  • "Does the login have sysadmin role on the linked server?"

I already have my workaround: parameterized queries bad - use ad-hoc queries.

I was hoping for an explanation of the thing that makes no sense. And hopefully if we have an explanation we can fix it - rather than workaround it.

Bonus Reading

"Too localized, unlikely to help others." Gives 7 links of other people having the issue. I hate people.

5
  • 3
    This doesn't immediately repro for me (from SQL Server 2019 to SQL Server 2017). Perhaps add all the details needed to repro to your question. Scripting the linked server, the tables & statistics, connect as permissions and specifying the versions would be good. Maybe provide a plan too. I know this issue can happen but I don't have unlimited time to build a minimal reproducible example.
    – Paul White
    Sep 16 at 17:30
  • @PaulWhite It doesn't reproduce for me in other environments. So we're left trying to imagine why a database server would ever issue a "Remote Scan"
    – Ian Boyd
    Sep 16 at 18:15
  • 1
    @IanBoyd - Paul's suggestion to add the details to your question are so we can actually help determine the particular local factors that are prompting SQL Server to choose to do a remote scan. If you add the requested details, perhaps we can help you with this exact situation.
    – Hannah Vernon
    Sep 16 at 18:53
  • @HannahVernon Unfortunately i can't even reproduce it myself in a lab.
    – Ian Boyd
    Sep 16 at 19:34
  • @PaulWhite Indeed, HYDROGEN is a server name,, which is running an instance of SQL Server.
    – Ian Boyd
    Sep 17 at 19:42
2

I talk a little about the remote join hint here, people can speculate but if they can't reproduce it's pretty tough to give you an answer.

In the meantime, you can use sp_executesql in a slightly different way to take away SQL Server's choices between bringing any data between the servers:

EXEC [Hydrogen].[CRM].sys.sp_executesql 
  N'UPDATE [dbo].[Customers]  SET [EyeColor] = @P1  
    WHERE [CustomerID] = @P5',  
  N'@P1 varchar(4),@P5 bigint', 
  'Blue',
  619;
1
  • The query isn't generated by me directly; it's the SqlClient's parameterized query that generates the pseudo-call to sp_executesql. My workaround of abandoning parameters also works as a workaround.
    – Ian Boyd
    Sep 16 at 19:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.