Implicit varchar to nvarchar conversion by linked server

  • 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.

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

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

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

  • 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.

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

    _____________
    Code for TallyGenerator

  • 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".

  • 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.

  • 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.

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

  • Could you try

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

    ?

    _____________
    Code for TallyGenerator

  • 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".

  • 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".

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

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