Conversion Strikes Again

While doing some performance tuning this week I found a query that was executing very frequently from a web application.  The query was doing a simple select from the base table with two columns in the where clause both of which were in the same index.
The query in question went something like this:
select contactid, documentid, documenttype from documents where contactid = ? and documentid = ?

Profiler showed the query is this format:
exec sp_executesql N' SELECT contactid, documentid, documenttype from Documents WHERE ContactID = @ContactID   AND DocumentID = @DocumentID', N'@ContactID nvarchar(36),@DocumentID int', @ContactID = N'464906a7-5acc-44a0-9b50-76da83ba05d8', @DocumentID = 280

The column ContactID is defined as a varchar(36) in the documents table.  When the value to compare to comes in as nvarchar a conversion is required and the index is lost, a table scan ensues and reads skyrocket.

The performance for the query is not that bad, but when that much data must be moved into memory to check for a match other items get pushed out.

The fix the developer decided to go with was to change the column definition to nvarchar from varchar.  This allowed the index to be used and the reads to drop significantly.

In my environment we are finding more and more places where conversion required for the where clauses are causing indexes to be ignored in favor of a less efficient index or a table scan.