Beware of CONVERT_IMPLICIT in a SQL Execution Plan

I was troubleshooting a stored procedure for a developer today, and I discovered that part of the query was doing an index scan against a fairly large table. Initially this did not make sense, since we had a non-clustered index on the column that was used in the WHERE clause of the query.

Looking a little deeper, I noticed that the execution plan had a CONVERT_IMPLICIT operator, which was causing the index scan. This was happening because the input parameter that was being used in the WHERE clause was an NVARCHAR(50), while the actual data type in the underlying table was a VARCHAR(50). Changing the input parameter to a VARCHAR(50) eliminated the implicit conversion, which allowed SQL Server to do an index seek instead of an index scan. Logical IOs went from 20765 to zero, for the table in question.

 

The underlying lesson is to be careful about data types in order to avoid these sorts of problems.

Technorati Tags:

This entry was posted in SQL Server 2008. Bookmark the permalink.

2 Responses to Beware of CONVERT_IMPLICIT in a SQL Execution Plan

  1. Rob says:

    Good post. This one trips up a lot of people.

  2. matt.bowler says:

    Thanks Glenn. That’s an excellent gotcha to be aware of.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s