Query Tuning – Implicit Conversion

In this blog, we are going to discuss ‘Implicit Conversion’ inside SQL Server.
Let us first understand the concept of Implicit Conversion. When SQL Server is comparing two expressions, the data types of both expressions should be the same. If not, SQL Server has to convert the data type of one of the expressions to match the data type of the other. Usually, a lower precedence data type is converted to a higher precedence data type to avoid any data loss. This is called implicit data type conversion or simply implicit conversion. Implicit conversions may hurt query performance. Microsoft Docs has a detailed page stating the valid conversions between different data types.
Conversions can happen in two ways – Implicit & Explicit. In the case of Implicit, the SQL Server engine will perform the conversion automatically as long as it is valid. In the case of Explicit conversions, we use functions like ‘CAST or ‘CONVERT which tells SQL Server explicitly to convert from one data type to another data type.
Explicit conversion is marginally better as in the case of implicit conversion SQL Server has to internally figure out which is the right data type to convert, which may lead to some query performance issues. Few of them are non-SARGability and extra CPU consumption.
We are using AdventureWorks2012 for this demo. We will enable STATISTICS TIME which will provide us with the metrics to evaluate execution performance.
To begin with, we have a simple query, that joins two tables Sales.Customer and Person.Person and filters on AccountNumber.
From the object explorer, we can verify the data type of AccountNumber column which is VARCHAR(10).

For the demo, assume that the value passed is Unicode. Note the value at the WHERE predicate is preceded with N.

As we discussed earlier, whenever there is a data type mismatch, SQL Server will convert data type with lower precedence to higher precedence. In this case, ‘varchar’ to ‘Nvarchar’ which, in turn, affects CPU performance as there will be multiple CPU cycles required for this.

Now, we turn on the execution plan and run the query. The query executes relatively fast and we are provided with the following output.

-- Enable Actual Execution Plan Before Executing The Query
USE AdventureWorks2012
GO

SELECT per.FirstName , per.LastName , per.BusinessEntityID,
cust.AccountNumber, cust.StoreID
FROM Sales.Customer AS cust
INNER JOIN Person.Person AS per ON cust.PersonID = per.BusinessEntityID
WHERE AccountNumber = N'AW00029594'
GO

If we move over to the ‘Messages’ tab we can see that the CPU time has a value of 94ms, which is primarily due to the implicit conversion.

Meanwhile, if we take a glimpse into the execution plan, it looks something like this –

The first thing to notice here is the warning symbol on the SELECT operator. If we look into the message, it shows us the following –

Here, two things can happen due to implicit conversion, the choices of an index may be affected causing index performance issues and the second is extra CPU cycles being consumed.

Thus, we must take the necessary steps to fix the issue and perform explicit conversion if that is an option. Here, the fix is very simple. Removing the ‘N’ to revert it to varchar solves the issue and if we re-execute the query, we see that negligible CPU cycles have been used and the warning in the execution plan is no longer there.

-- Enable Actual Execution Plan Before Executing The Query
USE AdventureWorks2012
GO

SELECT per.FirstName , per.LastName , per.BusinessEntityID,
cust.AccountNumber, cust.StoreID
FROM Sales.Customer AS cust
INNER JOIN Person.Person AS per ON cust.PersonID = per.BusinessEntityID
WHERE AccountNumber = 'AW00029594'
GO

To understand the impact of implicit conversions, let ‘s run the query simulating multiple users.

We can simulate this by running the same query about 200 times, and checking for execution time taken in both cases – with and without implicit conversions.

We are performing a very basic simulation here. A more advanced one can be performed using RML Utilities, using the OStress Tool where it is possible to simulate 100 or more users running this query over 100 times, which will prove to be more efficient in case of real-time performance comparisons. Upon execution, we find that it has taken 37s to complete whereas the query that does not perform implicit conversion just competes in 7s.

-- Enable Actual Execution Plan Before Executing The Query
USE AdventureWorks2012
GO

SELECT per.FirstName , per.LastName , per.BusinessEntityID,
cust.AccountNumber, cust.StoreID
FROM Sales.Customer AS cust
INNER JOIN Person.Person AS per ON cust.PersonID = per.BusinessEntityID
WHERE AccountNumber = N'AW00029594'
GO 200

-- Enable Actual Execution Plan Before Executing The Query
USE AdventureWorks2012
GO

SELECT per.FirstName , per.LastName , per.BusinessEntityID,
cust.AccountNumber, cust.StoreID
FROM Sales.Customer AS cust
INNER JOIN Person.Person AS per ON cust.PersonID = per.BusinessEntityID
WHERE AccountNumber = 'AW00029594'
GO 200

We can also explicitly convert the data type using the CONVERT function like below to save the CPU cycles.

Upon execution, we see that the execution completes in about 7 seconds which tells us that SQL is comfortable with explicit conversions.

USE AdventureWorks2012
GO

SELECT per.FirstName , per.LastName , per.BusinessEntityID,
cust.AccountNumber, cust.StoreID
FROM Sales.Customer AS cust
INNER JOIN Person.Person AS per ON cust.PersonID = per.BusinessEntityID
WHERE AccountNumber = CONVERT(VARCHAR, N'AW00029594')
GO

In conclusion, implicit conversion is not healthy for our SQL Server and there are many different ways on how we can fix this – such as examining the execution plan or even the plan cache where we can shred the plans in XML or use Extended Events which could trap all such workloads.