As I have written previously, the Implicit Conversions can be a performance problem and they can be easily avoided.
Also, as a next step on the road of cache analysis, here is a script which will show the Implicit Conversions in the “snapshot of your cached plans”.
WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), CachedPlansCTE ( ScalarOperator, QueryText, QueryPlan, CacheObjectType, ObjectType) AS ( SELECT RelOp1.op.value(N'@ScalarString', N'varchar(500)') AS ScalarOperator, qp.TEXT AS QueryText, qp.query_plan AS QueryPlan, qp.cacheobjtype AS CacheObjectType, qp.objtype AS ObjectType FROM CachedPlans qp CROSS APPLY qp.query_plan.nodes(N'//ScalarOperator') RelOp1 (op) ) SELECT TOP 50 QueryPlan, QueryText, ScalarOperator FROM CachedPlansCTE WHERE (CacheObjectType = N'Compiled Plan' AND ScalarOperator like '%CONVERT_IMPLICIT%') OPTION (MAXDOP 1) |
