Type conversion in expression may affect "Cardinality Estimate" in querY plan choice

This blog post is about the conversion between implicit and explicit conversion within SQL Server.

Introduction

The query performance issues are one of the most challenging processes for any database administrator. Poor query performance can cause massive problems and affect the entire SQL Server instance performance. That's why every DBA has some knowledge of query performance troubleshooting methodologies.

Indeed, there are many factors and reasons which affect query performance. However, SQL execution plans provide insight into the main principles or techniques used to solve these problems. By using an understanding of and interpreting the SQL Execution Plan for your query, you can uncover potential performance problems based on well-organized information about how the plan interprets your query. After all this brief information about how to optimize SQL queries, we will discuss and learn more details about a particular problem related to database performance called "implicit conversion".

When using SQL Server, you may see the warning message Type conversion in expression may affect "CardinalityEstimate" in query plan choice. A warning message tells you that the query execution engine cannot optimize a query plan that uses a column of one type as though it were a column of a different type. The message is accompanied by an execution plan that contains a table scan, which is not the most efficient way to retrieve data from a SQL Server table. This blog will explain why this warning message occurs and how you can prevent it from appearing.

Implicit Conversion

Let's run the following T-SQL query with the actual execution plan in SSMS.

SET STATISTICS IO, TIME ON   
GO   
SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle  
FROM HumanResources.Employee  
WHERE NationalIDNumber = 14417811 
GO

The actual plan shows the query uses Clustered Index Scan.

The select statement shows a warning message, and if you hover the mouse, the warning relates to the implicit conversion.

The error message says that type conversion might affect the cardinality estimate in query plan choice.

The task of the Cardinality Estimator determines the number of rows that will be returned from the query. The estimation directly affects the ability of the query optimizer to choose the proper index or create an optimized execution plan.

Type conversion in expression (CONVERT_IMPLICIT(int,[AdventureWorks2019].[HumanResources].[Employee].[NationalIDNumber],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(int,[AdventureWorks2019].[HumanResources].[Employee].[NationalIDNumber],0)=[@1]) may affect "SeekPlan" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(int,[AdventureWorks2019].[HumanResources].[Employee].[NationalIDNumber],0)=(14417811)) may affect "SeekPlan" in query plan choice

Let's understand why we get this implicit warning in this SQL query. As shown below, the data type of the [NationalIDNumber] column is NVARCHAR. However, we specified the INT value in the where clause. Therefore, SQL Server has to do the additional task of converting the value, and it might lead to higher CPU, IO, or memory.

The query performed 9 logical reads and 106 ms elapsed time.

We can modify the where clause predicate to search the NCARCHAR value directly, and it eliminates the IMPLICIT CONVERSION.

SET STATISTICS IO, TIME ON   
GO   
SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle  
FROM HumanResources.Employee  
WHERE NationalIDNumber = N'14417811'
GO

The modified query did not show any warning symbol, and it uses an INDEX SEEK instead of an INDEX SCAN. We did not change anything in the indexes. However, SQL Server creates an optimized plan by simply modifying the where clause predicate.

Now, we have only 2 logical reads and 93ms elapsed time.

Explicit Conversion

There might be a few scenarios where you cannot modify the supplied value. In this case, you might use EXPLICIT CONVERSION to avoid SQL Server spending time on implicit conversion.

SET STATISTICS IO, TIME ON  
GO   
SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle  
FROM HumanResources.Employee 
WHERE NationalIDNumber = CONVERT(NVARCHAR(15),14417811)
GO

SQL Server was able to generate the optimized plan (INDEX SEEK) using Explicit Conversions.

If you want to get a list of queries that used IMPICLIT_CONVERSION in SQL Server, get data from the following DMV’s.

SELECT DB_NAME(sql_text.[dbid]) AS DatabaseName,
sql_text.text AS [Query Text],
query_stats.execution_count AS [Execution Count], 
execution_plan.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS query_stats WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS sql_text 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS execution_plan
WHERE 
CAST(query_plan AS VARCHAR(MAX)) LIKE ('%CONVERT_IMPLICIT%')
AND 
DB_NAME(sql_text.[dbid])='AdventureWorks2019'  -- Note Modify the database name 
AND 
CAST(query_plan AS VARCHAR(MAX)) NOT LIKE '%CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS sql_text%'

The following query uses implicit_conversion in my SQL Server demo instance. You can get the same data and try to minimize or remove IMPLICIT CONVERSION in SQL Server.

This article explored the Implicit and Explicit conversion in SQL Server. You should modify queries to avoid any implicit conversions that might force SQL Server to choose improper execution plans with high resource consumption.