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.