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. 

 

http://msdn.microsoft.com/en-us/library/ms187746(v=sql.110).aspx

 

 

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.

--DBCC FREEPROCCACHE

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 

DECLARE @dbname SYSNAME

SET @dbname = QUOTENAME(DB_NAME()) ;

 

WITH XMLNAMESPACES

       (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

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

        t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]',

                'varchar(128)') ,

        t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]',

                'varchar(128)') ,

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

                '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 ,

        query_plan

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]',

                                                              'varchar(128)')

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

                                                              'varchar(128)')

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

                                                              'varchar(128)')

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 3.3.1.1

 

In 3.3.1.1 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 3.3.1.0 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 3.3.1.1 (and beyond) as these implicit conversions have been resolved.