SQL SERVER – How to Fix CONVERT_IMPLICIT Warnings?

One of the biggest issues I see with at various of my customer who signed up at Comprehensive Database Performance Health Check is about CONVERT_IMPLICIT. As soon as I demonstrate my customer that they are not getting performance due to CONVERT_IMPLICIT their next question is how can they fix the issue of CONVERT_IMPLICIT. In this single blog post, we will learn about how to find this issue and fix it as well.

Before we continue with the blog post, I must reference two different blog post, I have used as a reference to this blog.

• Find All Queries with Implicit Conversion in SQL Server
• Simple Explanation of Data Type Precedence

Now let us discuss this blog post

Here is the script which you can run and identify queries with implicit conversion.

-- (c) https://blog.sqlauthority.com
SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], 
t.text AS [Query Text],
qs.total_worker_time AS [Total Worker Time], 
qs.total_worker_time/qs.execution_count AS [Avg Worker Time], 
qs.max_worker_time AS [Max Worker Time], 
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], 
qs.max_elapsed_time AS [Max Elapsed Time],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.max_logical_reads AS [Max Logical Reads], 
qs.execution_count AS [Execution Count], 
qs.creation_time AS [Creation Time],
qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE ('%CONVERT_IMPLICIT%')
 AND t.[dbid] = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

Once you run above script, you will be able to identify all the queries with the execution plan which are doing convert implicit operation.

Now let us try to answer few important questions related to CONVERT_IMPLICIT.

Q: What is CONVERT_IMPLICIT?
A: When SQL Server encounters two different datatype’s comparison in either WHERE clause or JOIN clause, one side of the values will be converted to the datatype of another side. This process is very CPU, IO and Memory consuming.

Q: What is the negative impact of CONVERT_IMPLICIT?
A: 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.

Q: How to Fix CONVERT_IMPLICIT warnings?
A: There are various ways to fix this error:

Method 1: 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.

You can see here how we can fix this issue by changing the datatype: Simple Explanation of Data Type Precedence.

Here is a small demonstration for the same:

First create a sample data:

CREATE TABLE FirstTable (ID INT, Col VARCHAR(100))
GO
INSERT INTO FirstTable (ID, Col)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third'
GO

Next run following query:

SELECT *
FROM FirstTable
WHERE ID = N'1' AND Col = N'First'
GO

You can see in the resultset there is a CONVERT_IMPLICIT warning. As know that our ID is INT and Col is VARCHAR. Now we will remove the usage of N’s before that variable. Whenever we use N before a variable it converts that variable into Unicode (NVARCHAR).

Now run the following query:

SELECT *
FROM FirstTable
WHERE ID = 1 AND Col = 'First'
GO

You will see that from the execution plan there is no CONVERT_IMPLICIT warning. You can see it is indeed very easy to fix this warning and set your database for optimal performance.

Method 2: Computed columns
I have seen quite a few people using this method where they create computed columns and indexes over it. I personally do not like this method as it is not very easy to do as well as involves adding index which can slow down the entire system. I would prefer to use method 1 only.

Method 3: Indexed views
If you are reading my blog, you may be familiar that I am not a big fan of Views and particularly for indexed views. This is my least preferred way to solve the issue.

Well, that’s what I wanted to cover in this blog post. In the future blog post we will discuss similar interesting subjects.

Reference: Pinal Dave (https://blog.sqlauthority.com)