Finding Implicit Column Conversions in the Plan Cache

Last year Tibor Karaszi posted a blog post titled Match Those Types that talked about implicit data type conversions associated with mismatched data types between filter criteria and table column data types.  His blog post does a good job of covering the topic so I am not going to rehash it out here.

However, at the time I was working on a number of scripts that query information from the plan cache in SQL Server 2005 and 2008 for a presentation I had in mind but never got around to actually doing.  I mentioned this in the comments on the blog post and yesterday I was contacted by email asking if I would share the script.  I dug it out, and it wasn’t exactly complete, but I decided to send what I had to the requestor any way. 

Being the person I am, I was somewhat bothered by the half completed script so I put a little time (ok, so in reality was a lot more than I originally planned to) into finishing the script off to where I originally wanted it to be.

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 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.

7 thoughts on “Finding Implicit Column Conversions in the Plan Cache

  1. It’s great post again Jonathan.

    Just wondering ,if the statement is part of any procedure , is there a way we could easily display the proc name as well ?

    Would it work if joining to sys.dm_exec_sql_text DMV ?

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

    Thanks
    Raj

  2. Great post Jonathan – if you find yourself with a little more time, it’d be helpful if you could add a case statement for the ConvertedFrom and ConvertedTo to check for decimal or numeric and include the precision and scale (when they don’t match).

  3. Jonathan, thank you so much. This is very helpful for you to present ready to run like this so we don’t have to.
    Question: Based on the work you did here: https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/
    Might I be able to disregard conversions like bit to int or what am I missing?
    I was experimenting with explicitly defining variables like @True bit = 1 in procs and using those in predicates instead of ints 0 or 1 to clean up “IMPLICIT” from showing up – partly to cut the noise down, too.

  4. Hello Jonathan,

    I am facing an issue , while running the above code, i got list of queries which are victim of implicit conversion.
    but when i checked the Execution plan there was no implicit conversion.
    later i tried to check XML execution plan , i found implicit conversion but it was under index seek.
    Not sure what to do here.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.