SQL Server showing nvarchar for the variables declared as varchar in the code

Question

I have a typical scenario where in the SQL Server execution plan i can see there is implicit type conversion to nvarchar. Table field has varchar data type and in the code also it is declared as varchar data type as well. It is QL Server 2017 RTM CU21 and it Azure VM. Because of this implicit conversion to nvarchar index is not being used and is causing performance issues such as locking and blocking. I have not seen like this before. The code is written in MyBatis framework. I am kind a lost why it is happening like that.

I appreciate any help and guidance on this typical case.

Thanks in advance.

Answer 1

Hi @AC ,

>What is CONVERT_IMPLICIT?

Implicit conversions generally happen when, in a WHERE or JOIN clause filter of a query, you specify a column with a datatype that doesn’t match the datatype of the column in the table. When two items of data with different data types are being compared in SQL, all the values with the lower-precedence datatype must be converted up to the higher precedence type before the comparison can proceed, and the filter applied.

>What is the negative impact of CONVERT_IMPLICIT?

Whenever CONVERT_IMPLICIT function encountered by a query, usually it takes way more resources than the normal case. The usage of CPU, IO and Memory goes high. Additionally, if the conversion is happening over the column of the table, all the values of the column will be converted to new datatype which will slow down your query in proportion to a size of the table.

Additionally, CONVERT_IMPLICIT is a function and whenever it is used on the column, it also it negatively impacts on execution plan by not selecting the optimal index for the query.

>How to Fix CONVERT_IMPLICIT warnings?

One method is Match the datatype.

Match the datatype of the columns and values used in the query where the comparison is happening. In JOINS as well as in the WHERE clause, make sure that both the side of the comparison have the same datatypes. This is the simplest and the most efficient way to fix the issue.

>Detecting SQL Server Implicit Conversions

We can use Extended Events to detect when implicit conversions are occurring.

Please refer to below blogs to get more detail information.

SQL SERVER – How to Fix CONVERT_IMPLICIT Warnings?
SQL Server Implicit Conversions Performance Issues
What Is Implicit Conversion In SQL Server
When SQL Server Performance Goes Bad: Implicit Conversions

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

Hi Cathy,

I understand your suggestion. My problem is different though, in the code for the data type, there is varchar(200), the table field data type is varchar(200).

When i look at the query plan from activity monitor and from the query store, this particular query is taking time, and i see that field has nvarchar in the query, and the execution plan has implicit conversion, i am trying to find out where is that nvarchar is getting from.

Is there a setting somewhere in SQL Server that does this?

Answer 3

Most likely MyBatis is using sp_executesql or sp_prepare and pass these values as nvarchar. I.e., SQL server has to convert the column side to nvarchar, anything else would go against the rules for data type precedence.

You can get proof for this by capturing a call from MyBatis in a trace and investigate that call.

In the end, if my guess is right (which it probably is ;-) ), you have to talk to the MyBatis people and see how they can match the column type when passing the paramaterized value to SQL Server.

Answer 4

Parameter types are controlled by the client. It seems the MyBatis framework is declaring parameters as nvarchar instead of varchar as expected. Knowing nothing about MyBatis, I can't provide specific guidance on how to fix that.

All that can be done on the server side is to use stored procedures so that mismatched types will be converted before the proc executes. That will of course require app code changes. Stored procedures also have many other benefits but mitigate the utility of ORM frameworks.

Answer 5

Dan, in the MyBatis, code it is declared as varchar. Is it possible that MyBatis is overwriting that explicit declaration of varchar data type and making them nvarchar? If this is the case, that makes sense but i could not find anything that pertains to this assumption.