Fix implicit conversion when you can’t change the query

In this post I cover ways to fix performance problems caused by implicit conversion when the query cannot be modified.

What is an implicit conversion?

I’ve already covered, in detail, implicit conversions in SQL Server in a previous blog post.

But, a brief description is: an implicit conversion occurs whenever SQL Server has to convert, behind the scenes, the values in a column from one data type to another in order to match the data type of another value or column that it’s being compared against.

Why wouldn’t you be able to change the query?

The two most common scenarios I’ve ran into are:

• the software vendor does not want to change the code
• a legacy application that’s no longer maintained and nobody has access to the code base

The query

The database I’m using for this is AdventureWorks2019.

Before running this query, I also set STATISTICS IO to ON.

The result and execution plan look like this.

The IO stats output looks like this.

(1 row affected)
Table ‘Product’. Scan count 0, logical reads 2, physical reads 0, page server reads 0,
read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table ‘SalesOrderDetail’. Scan count 1, logical reads 3, physical reads 0, page server reads 0,
read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table ‘SalesOrderHeader’. Scan count 1, logical reads 689, physical reads 0, page server reads 0,
read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

At a first glance it might look like the fix would be an index on the CreditCardApprovalCode. This should fix the Clustered Index Scan that ends up reading 689 8KB pages from the SalesOrderHeader table, right?

Fixing performance

The first attempt

So, let’s add the index.

I clear the buffer and plan cache.

And then I rerun the query.

We’re still getting an index scan, but this time on the newly created index.
One minor improvement is that the index scan operator is no longer pushing 31465 records down the line.
And, as a result, we’re down two operators, a Compute Scalar and a Filter.

Notice that yellow Warning sign there? I’ll come back to that.

Page-wise, we’re down 70%, to 201 8KB pages out of the initial 689 read from SalesOrderHeader.

(1 row affected)
Table ‘Product’. Scan count 0, logical reads 2, physical reads 2, page server reads 0,
read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table ‘SalesOrderDetail’. Scan count 1, logical reads 3, physical reads 3, page server reads 0,
read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table ‘SalesOrderHeader’. Scan count 1, logical reads 203, physical reads 1, page server reads 0,
read-ahead reads 201, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

But that’s just because that’s the total number of pages the new index has.
This can be easily verified with an index visualization query.

An index visualization query is a query that that allows you to see the data that’s in an index by only using that index to satisfy all the query’s requirements.

The query only uses the newly created nonclustered index to provide the required output.

And this is how the IO stats output looks like.

(31465 rows affected)
Table ‘SalesOrderHeader’. Scan count 1, logical reads 203, physical reads 1, page server reads 0,
read-ahead reads 201, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

This confirms that the entire index is comprised of 203 8KB pages.

So what about that warning sign?

Hovering over the SELECT operator reveals what that warning is about and why the query scans the entire index instead of seeking to the specific value.

There are actually two warnings, the first one is cause by the fact that the SalesOrderNumber column in Sales.SalesOrderHeader is a computed column defined like this:

The warning that actually matters is the second one.
In this case, it points out that there’s an implicit conversion occurring for the CreditCardApprovalCode column.
This is why the query ends up scanning the whole index.

How to actually fix the implicit conversion without changing the query

First, I drop the previously created index.

Next, I add a computed column that converts the values from the CreditCardApprovalCode column into NVARCHAR(15), the same data type as the local variable used in the query’s WHERE clause.

Lastly, I recreate the index, but on the computed column instead of the original column.

And now, I rerun the query and check the execution plan.

The optimizer is smart enough to figure out that the new index is the best option and, as a result of the index using the computed column as a key column, it’s able to seek to that specific row and no longer read the whole index.

This is confirmed by the IO stats output.

(1 row affected)
Table ‘Product’. Scan count 0, logical reads 2, physical reads 0, page server reads 0,
read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table ‘SalesOrderDetail’. Scan count 1, logical reads 3, physical reads 0, page server reads 0,
read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table ‘SalesOrderHeader’. Scan count 1, logical reads 6, physical reads 0, page server reads 0,
read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

And the 8KB page reads from SalesOrderHeader are down to 6.

One thing to keep in mind

When it comes to these types of workarounds and strict vendors, you should temporarily drop the index and computed column before application upgrades and patches, as well as before requesting support from the vendor with any other issues, and then re-create them again afterwards.

Conclusion

If you can’t change the code it doesn’t automatically mean your out of options when it comes to fix implicit conversion when you can’t change the query.

The same solution can be applied for other cases where a function gets applied on a column used for filtering. For a similar demo, check out my post about finding long values faster.