Another quick post of simple changes you can make to your code to create more optimal execution plans. This one is on implicit conversions. An implicit conversion is when SQL Server must automatically convert a data type from one type to another when comparing values, moving data or combining values with other values. When these values are converted, during the query process, it adds additional overhead and impacts performance.
Here is a great chart by Microsoft that shows you conversions and which will cause an implicit or explicit conversion. In this post I will not go into explicit, just know that is what you explicitly tell SQL Server to CAST or CONVERT a value.
Image credit: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-2017
Let’s look at a very simple but common implicit conversion scenario. Here we have a table Employee with a NationalIDNumber column defined with a NVARCHAR data type. In the query we will use a WHERE clause to search for a specific ID.
In the query below, we have requested NationalIDNumber equal to the integer value 14417807. For SQL Server to compare these two data types it must convert that NVARCHAR to a INT. Which means every value in the that column must go through a conversion process which causes a table scan.
USE AdventureWorks2016CTP3 GO SET STATISTICS IO ON GO SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle FROM HumanResources.Employee WHERE NationalIDNumber = 14417807
In the execution plan you will see an exclamation point warning you that there is a potential issue with the query. Hovering over the SELECT operator, you will see that a CONVERT_IMPLICIT is happening which may have affected the optimizer from using a SEEK.
(1 row affected)
Table ‘Employee’. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Now the question is how we fix it. It’s really simple but it does require a code change. Let’s look back at our query.
SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle FROM HumanResources.Employee WHERE NationalIDNumber = 14417807
Remember we asked for a integer value. Just by add single quotes to the value we can eliminate our issue. It’s important to always know what data types your columns are when querying them. In this case since it is a NVARCHAR all I need to do is supply a character value. This is accomplished by adding single quotes around the value.
SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle FROM HumanResources.Employee WHERE NationalIDNumber = '14417807'
It’s simple to see the results. Note above the Scan count 1, logical reads 9, physical reads 0. When we rerun it we get the below.
(1 row affected)
Table ‘Employee’. Scan count 0, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
You can also wee in the graphical plan that the warning is now gone, and we have a SEEK instead of the SCAN which is much more efficient.
The only thing left to clean up in this plan is the key lookup. To fix that take a look at my blog from last week here. There are many ways you can end up with implicit or explicit conversion issues and the additional overhead they can create. In most cases they are extremely easy to fix with a little code change, this was just one example. To help you find these in your environment Jonathan Kehayias has written a query to find column side implicit conversions in your plan cache be sure to check it out.