SQL Server Implicit Conversions Performance Issues

By: K. Brian Kelley   |   Updated: 2019-10-23   |   Comments   |   Related: > Data Types

Problem

I've heard implicit conversions in T-SQL code are bad for SQL Server performance, but I don't understand how and when they occur. I also don't know how to detect if they are occurring in my SQL Server queries. Can you explain what they are and how I might spot them?

Solution

Implicit conversions occur when SQL Server has to do a conversion from one data type to another data type to be able to make a comparison and that conversion wasn't specified in the query. These hidden conversions can be a performance killer, especially if SQL Server has to apply them to every row to perform that comparison.

SQL Server Implicit Conversion Example

One easy way to see this implicit conversion is with the sql_variant data type. Any comparison to a value that is of that type requires a conversion. Therefore, we'll see an implicit conversion with the following query:

-- This will generate an implicit conversion because of the sql_variant data type.
DECLARE @SalesDate sql_variant; 
SET @SalesDate = '20130731';
 
SELECT SalesOrderID, ModifiedDate 
FROM Sales.SalesOrderDetail
WHERE ModifiedDate >= @SalesDate;

And if we look at the generated execution plan, we'll see that we end up with a clustered index scan and a compute scalar as well as a filter operator:

Compare that with this query that functionally does the same thing:

-- This will not generate an implicit conversion on the table side
DECLARE @SalesDate VARCHAR(20); 
SET @SalesDate = '20130731';
 
SELECT SalesOrderID, ModifiedDate 
FROM Sales.SalesOrderDetail
WHERE ModifiedDate >= @SalesDate;

When we view its execution plan, we still see the clustered index scan, but we don't see the other two operators:

When we do a comparison of the cost, the conversion in this case doesn't generate a lot of overhead. However, a lot of 6% performance overheads add up. We're also talking about a relatively simple query. Needless to say, we want to avoid implicit conversions.

Avoiding SQL Server Implicit Conversions

Let's look at the following query, which is for any of the more recent SQL Server versions of the AdventureWorks database:

-- This will cause an implicit conversion on the column
SELECT CreditCardID, CardNumber
FROM Sales.CreditCard
WHERE CardNumber = 11119775847802;

At first glance, we don't see any cause for concern with request to the query. However, if we were to look at the data type for CardNumber, we'd find that it's actually an nvarchar data type. Therefore, SQL Server is going to perform a conversion:

Or does it? We don't see the compute scalar and filter operators like we did with the sql_variant example. We actually have to look at the information with respect to the nonclustered index scan itself. If you hover over the Index Scan operator, the following will display:

Note the formula:

CONVERT_IMPLICIT(numeric(14,0),[AdventureWorks2017].[Sales].[CreditCard].[CardNumber].0)

There's the implicit conversion! A clue that something like that was happening is the fact that the operator was an index scan, not a seek. We would expect that there would be an index on CardNumber, given how we typically ask for data. So to have a scan instead of a seek would indicate that there was probably a Row-By-Agonizing-Row (RBAR) operation going on. Once we looked at the details of the scan operator, we can see that's exactly what occurred.

How do we avoid the implicit conversion? The key is to specify the conversion where it will have the least impact. For this particular table, we know that the column is of type nvarchar. In order to avoid the comparison against every row, let's perform the conversion on the value being compared. Therefore, we'll need to add an explicit conversion on the number, changing it to nvarchar to match the column's data type. That results in the following query:

-- Because we are explicitly converting the scalar value, no implicit conversion happens
SELECT CreditCardID, CardNumber
FROM Sales.CreditCard
WHERE CardNumber = CONVERT(NVARCHAR(14), 11119775847802);

When we perform the explicit conversion on the value, we only require one additional calculation. When SQL Server performed the implicit conversion, we incurred almost 2000 additional calculations, one for each row of the table. If our explicit conversion on the value worked, we should see an index seek. In fact we do:

Note also that the operator information tells us that instead of processing 1,911 rows, we only processed 1. That's what we'd expect. That simple change to our query resulted in a completely different execution plan and far fewer rows than the implicit conversion.

This is the best way to handle when you find cases where SQL Server is performing an implicit conversion. Determine what explicit conversion you can do instead. Then, figure out which is the least costly in the general case. Here, the answer was quite simple as there was only one conversion to perform and it was on the scalar value. More complex queries will likely require a bit more work.

Detecting SQL Server Implicit Conversions

Is there any easy way to detect when implicit conversions are occurring? We can use Extended Events, but it's not going to catch every case. We can set up Extended Events which fire if there's a performance issue detected. Here's an example where we're writing to a ring buffer as a target. If you need to capture over time, you'd want to use a different target:

CREATE EVENT SESSION [Detect Conversion Performance Issues] ON SERVER 
ADD EVENT sqlserver.plan_affecting_convert(
    ACTION(sqlserver.database_name,sqlserver.sql_text)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2017'))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
     MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

As you might guess, this was simply scripted. If you're setting up for yourself, here's the setting you'll want to add:

This extended event will include a lot of noise. That's because any use of SSMS or like clients issue queries which produce implicit conversions as well, especially as they query the various catalog views. Therefore, you may have to export the XML and search for the specific object names. Here's an example where we see the implicit conversion from the sql_variant example before:

Concluding Thoughts

Implicit conversions can result in performance issues we don't expect. This is especially true when the implicit conversion causes SQL Server to have to perform a RBAR operation, such as converting the value on every row. By looking at execution plans and potentially through the use of a particular extended event, we can spot when these implicit conversions occur.

If we detect an implicit conversion in our query, the best way to approach solving this issue is to determine if we can put an explicit conversion somewhere else. For instance, if we have a scalar value, rather than allowing SQL Server to convert the column to match the data type of the scalar, we can put an explicit conversion to change the scalar to match the data type of the column. When we can do this, we avoid the RBAR operation, which allows SQL Server to choose index seeks, to eliminate extra operations, and carry out the query with fewer overall resources.

Next Steps

• Learn how certain functions, like CONCAT(), will cause implicit conversions.
• Read about other reasons why a poorly written query causes SQL Server not to use an index seek.
• Know your data type precedence, as that determines the conversions.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips