Performance Degradation due to Implicit Conversions in Microsoft SQL Server

1 like Updated 2/5/15 11:27 AM by ChrisEllisTags:

Implicit Conversions


When SQL Server tries to join on or compare fields of different data types, if they are not the same data type, it will convert one to match the other. This is called implicit conversion.


An implicit conversion is not desired in SQL Server, and can lead to poor performance use due to SQL Server not using indexes optimally.



How to Find Implicit Conversions


If there are implicit conversions, the plans generated may still be cached in SQL Server. The SQL below will show the plans with the implicit conversions. 



--Clear Proc Cache, do not run in production unless you intend to wipe out cached plans. This will require any new plans to recompile as they come in.






SET @dbname = QUOTENAME(DB_NAME()) ;



       (DEFAULT '')

SELECT  stmt.value('(@StatementText)[1]', 'varchar(max)') ,


                'varchar(128)') ,


                'varchar(128)') ,


                'varchar(128)') ,

        ic.DATA_TYPE AS ConvertFrom ,

        ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength ,

        t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo ,

        t.value('(@Length)[1]', 'int') AS ConvertToLength ,


FROM    sys.dm_exec_cached_plans AS cp

        CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')

        AS batch ( stmt )

        CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n ( t )

        JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]',


                                                 AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]',


                                                 AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]',


WHERE   t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1 




The results will be shown in the query results, if you click on the XML link, you can search for and observe the Implicit Conversion




What we changed in


In we altered the IBM TRIRIGA Platform Java code to match NUMERIC typed defined columns in the database. This allows SQL Server to use indexes correctly, and nearly eliminates the number of locks and blocks. In Platform and older, different data types like BIGINT, INTEGER, SMALLINT, etc were used to bind variables in sql statements.  Those have been replaced with NUMERIC, matching the table definition.  It is therefor very important to upgrade/patch your platform to (and beyond) as these implicit conversions have been resolved.