Implicit conversion is a common cause of performance degradation. It happens when a client/application runs a query in which a column is compared to a parameter with a data type that precedes the column data type. For example, nvarchar precedes varchar and having an application send nvarchar data to compare with varchar column will cause an implicit conversion. You can check this documentation for the data type precedence.
To be able to compare the column with the parameter, it is necessary to convert all the data in the column to the same data type as the parameter, which increases the CPU consumption and cause performance degradation.
When there is an implicit conversion for a query, we can see the following warning in the execution plan:
"Type conversion is expression (CONVERT_IMPLICIT …) may affect "" in query plan choice"
It’s possible to see the impact of implicit conversion in SQL Server Management Studio (SSMS) when you have a table with a varchar column and you compare the execution plans of a query that uses a nvarchar parameter to one that uses a varchar parameter to query the same data:
create table tb1(
col1 varchar(50)
)
--Create index on col1
create index idx1 on tb1(col1 asc)
--Compare varchar column with nvarchar parameter (implicit conversion)
declare @v2 nvarchar(50)
select * from tb1 where col1 = @v2
--Compare varchar column with varchar parameter (no implicit conversion)
declare @v1 varchar(50)
select * from tb1 where col1 = @v1
To see the execution plan for the two selects above, you can enable It in SQL Server Management Studio by pressing CTRL+M or the following button and then run the query:
If you look at the execution plans of the two selects above, you will notice that by changing the parameter data type from nvarchar to varchar, the cost is reduced considerably.
If there has been an implicit conversion and you place your mouse above the first operation of the execution plan, you will see the warning for implicit conversion (the ! symbol means there is a warning):
There were occurrences of this issue in which the query was not performing well from the application but had much better performance when running from SQL Server Management Studio (SSMS). This was because, to execute the query from SSMS, the parameter was being replaced with a value of the same data type as the column and therefore not reproducing the issue. To identify the issue, we had to check the execution plan used in the query store during the time the query was run from the application. For more information about query store, please check this documentation.
If you would like to see other queries that are run in the database for which implicit conversion might be affecting the execution plan, you can enable an extend event for the event 'sqlserver.plan_affecting_convert'. You can find more information about extended events for Azure SQL Database in this documentation.
To prevent the implicit conversion, you can change the data type in one of the sides to the same as the other. The ideal solution would be to change the parameter that is being declared by the application from nvarchar to varchar. However, if It's not possible to change the parameter at the application side, you could also change the data type of the column from varchar to nvarchar. Keep in mind this will double the space necessary for the data, including in the buffer cache, so it can impact the performance of other queries. Also, changing the data type will cause unavailability.
For applications using JDBC, there is a connection property that determines if the strings will be sent as unicode or not: sendStringParametersAsUnicode, as you can see in this documentation.
Be careful with any change you intend to implement and test It thoroughly in a non-production environment, before changing in production.
