In my previous blog, I discussed about “Which are the queries using a particular index or table?“. Today’s post, I am going to show how you can find all queries with Implicit Conversion in SQL Server.

What is an implicit conversion?

Implicit conversions occur whenever data with two different data types are being compared. Implicit conversions will often harm performance as the optimizer needs to convert between data types on-the-fly.

What is the disadvantage of Implicit Conversion for any query?

Whenever any query has to go through implicit conversion on any column, this can cause poor performance like scans instead of seeks, excessive reads, and unwanted slowness.

Script to find Queries With Implicit Conversion

use <DatabaseName>
go
SELECT OBJECT_SCHEMA_NAME (stx.objectid, stx.dbid) +'.'+OBJECT_NAME(stx.objectid, stx.dbid) AS object_name,
  SUBSTRING	(stx.[text],(eqs.statement_start_offset / 2) + 1,
				(CASE WHEN eqs.statement_end_offset =-1 
					THEN DATALENGTH(stx.text) 
					ELSE eqs.statement_end_offset 
					END - eqs.statement_start_offset
				 )/ 2 + 1) AS QueryText,
  CAST(pl.query_plan AS XML) AS sqlplan,
  stx.[text] as complete_text,
  eqs.execution_count,
  eqs.creation_time [compilation time],
  eqs.total_worker_time/execution_count AS avg_cpu_time,
  eqs.total_worker_time AS total_cpu_time,
  eqs.total_logical_reads/execution_count AS avg_logical_reads,
  eqs.total_logical_reads,
  eqs.last_execution_time
FROM sys.dm_exec_query_stats AS eqs
     CROSS APPLY sys.dm_exec_text_query_plan(eqs.plan_handle, 
					     eqs.statement_start_offset, 
					     eqs.statement_end_offset) AS pl
     CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS stx
WHERE pl.query_plan not like '%OBJECT_SCHEMA_NAME (stx.objectid, stx.dbid) %'
and pl.dbid=DB_ID()
and pl.query_plan like '%CONVERT_IMPLICIT%'

• To find “Finding Implicit Column Conversions in the Plan Cache”, you can use the below script. it is written by one of well known SQL experts Jonathan Kehayias.

Finding Implicit Column Conversions in the Plan Cache

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

This script queries the plan cache for query plans from the current database that have an implicit conversion on the column side of the query and returns the statement from the batch that is causing the conversion, the schema, tablename, and columnname being converted, as well as the original and converted datatypes and lengths and finally the entire query plan so that you can see whether it is an adhoc statement causing the conversion or if it is occurring inside of a stored procedure.

Happy Learning!