Implicit varchar to nvarchar conversion by linked server

Question

Post reply

Implicit varchar to nvarchar conversion by linked server

andrei_solntsev

SSCrazy

Points: 2177
More actions
April 23, 2021 at 4:01 am

#3874644
I have a remote query over linked server with search by varchar column. However, the linked server converts the parameter to nvarchar which causes high CPU usage and bad query performance on the remote server (the table is quite big). The linked server defined as following:
EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServer', @srvproduct=N'SQL Server'
Does anybody know, how to prevent this varchar to nvarchar conversion? I tried various combinations of Collation Compatible and Use Remote Collation options, but nothing helps.
- This topic was modified 3 years, 7 months ago by andrei_solntsev.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Is the data type on both local and remote VARCHAR or is one of them NVARCHAR

andrei_solntsev SSCrazy Points: 2177 More actions · Answer 2

It's varchar on both ends. However, collations are different. One on remote server is CS while local CI.

This reply was modified 3 years, 7 months ago by andrei_solntsev.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

And can you share the query which is using the linked server?

andrei_solntsev SSCrazy Points: 2177 More actions · Answer 4

Here is a sample query executed on a SQL Server:

LinkedServerQuery

This is how this query get actually executed on a remote server (caught by SQL Profiler). Please note that linked server driver added N in from of the parameter string.

ActualQueryOnRemoteServer

The next image shows that the destination table has an index on AccountNumber column. AcountNumber is defined as varcar(10).

Table

Below is an execution plan for this query. Please note that index scanned in full to get several million AccountNumbers, which then converted into nvarchar(10) in memory and then compared to the parameter value. This simple query generates high memory usage and high CPU load when executed frequently enough. A dozen of queries like this running simultaneously can "kill" the server.

ExecutionPlan

Ideally, it would be good to change definition of AccountNumber column to be nvarchar, but unfortunately, this is not possible.

I noticed that this implicit varchar to nvarchar conversion happens for all SQL versions and for all ODBC/OLEDB drivers which I managed to try for linked server.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Did you try to pass the parameter not via a literal constant but by a VARCHAR variable?

_____________
Code for TallyGenerator

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

I would think using the EXEC ... AT method, should get around that. Try that:

EXEC('SELECT ... FROM dbname.dbo.table_name ... WHERE ...') AT [RemoteServer]

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

andrei_solntsev SSCrazy Points: 2177 More actions · Answer 7

Thank you ScottPletcher, your method worked. The query was executed on remote server "as is". The only downside of this method is inability to use parameters. However, this is better than full table/index scan.

andrei_solntsev SSCrazy Points: 2177 More actions · Answer 8

Sergiy, below is what happens when parameter passed as variable

Here is the query:

QueryWithParameter

And here how it is actually executed on remote server:

RemoteQueryWithParameter

Please note that parameter is passed as nvarchar(10) by driver.

andrei_solntsev SSCrazy Points: 2177 More actions · Answer 9

For me it looks like a bug when driver converts parameter types like this.

Sergiy SSC Guru Points: 110208 More actions · Answer 10

Could you try

WHERE [AccountNumber] = CONVERT(varchar(10), @AN)

?

_____________
Code for TallyGenerator

ScottPletcher SSC Guru Points: 101098 More actions · Answer 11

andrei_solntsev wrote:

Thank you ScottPletcher, your method worked. The query was executed on remote server "as is". The only downside of this method is inability to use parameters. However, this is better than full table/index scan.

True, you have to substitute in the params yourself as you're building the string. Yeah, sorry, I don't know a way around that.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101098 More actions · Answer 12

andrei_solntsev wrote:

For me it looks like a bug when driver converts parameter types like this.

I tend to agree with you there, I think it's a type of bug as well.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".