Page History

You would expect an index seek to occur on the ‘AK_Employee_NationalIDNumber’ index; however, the execution plan clearly shows that an index scan is performed instead of an index seek.

To determine the cause of having an index scan, mouse over the index scan operation of the execution plan to display the tooltip containing additional details:

Under the ‘Predicate’ section of the tooltip see that the ‘CONVERT_IMPLICIT(int,[AdventureWorks].[HumanResources].[Employee].[NationalIDNumber],0)=(253022876)’ implicit conversion is being performed on the ‘NationalIDNumber’ column. This column value is being converted because the data type of the column mismatch the data type of the literal value being used in the compare. To resolve the data mismatch, change the data type of the literal value to match the data type of the column:

The new execution plan for the updated TSQL is the following:

Note that the index scan is replaced by an index seek operation, optimizing successfully the query. 

Use SQL Doctor to identify implicit conversions

IDERA SQL Doctor collects and analyzes your heaviest TSQL to help you identify implicit conversions along with other problems that impact query performance. The following is the resulting recommendation generated by SQL Doctor for the example presented above (SDR-Q36 – Implicit conversion on a column may be causing index suppression):

You can click Show me the problem on the Recommendations window to display the TSQL.