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]"]') = 1This 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.
More form comments
---
-- Overview CONVERT_IMPLICIT in _Current_ execution plans
---
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS PLN)
select CP.bucketid, CP.refcounts, CP.usecounts, CP.size_in_bytes, CP.memory_object_address, CP.cacheobjtype, CP.objtype, CP.plan_handle
, QS.sql_handle, QS.statement_start_offset, QS.statement_end_offset, QS.plan_generation_num
-- , QS.plan_handle
, QS.creation_time, QS.last_execution_time, QS.execution_count, QS.total_worker_time, QS.last_worker_time, QS.min_worker_time, QS.max_worker_time, QS.total_physical_reads, QS.last_physical_reads, QS.min_physical_reads, QS.max_physical_reads, QS.total_logical_writes, QS.last_logical_writes, QS.min_logical_writes, QS.max_logical_writes, QS.total_logical_reads, QS.last_logical_reads, QS.min_logical_reads, QS.max_logical_reads, QS.total_clr_time, QS.last_clr_time, QS.min_clr_time, QS.max_clr_time, QS.total_elapsed_time, QS.last_elapsed_time, QS.min_elapsed_time, QS.max_elapsed_time
, QP.dbid, QP.objectid, QP.number as QPnumber, QP.encrypted, QP.query_plan
-- , ST.dbid, ST.objectid
, ST.number as Stmt_Number, ST.encrypted as Stmt_Encrypted, ST.[text] as STMT_Text
-- , convert(nvarchar(max), QP.query_plan) as Query_Plan_Text
FROM sys.dm_exec_cached_plans CP
left join sys.dm_exec_query_stats QS
on cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) QP
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) ST
where query_plan is not null
--and query_plan.exist('/PLN:ShowPlanXML//PLN:MissingIndex ') = 1
-- find implicit conversions -- moet de xml-wijze nog zoeken
and convert(nvarchar(max) , QP.query_plan) like N'%CONVERT_IMPLICIT(NVARCHAR%' -- N'%CONVERT_IMPLICIT%'
-- Limit in time
and QS.last_execution_time > dateadd(dd,-14,getdate())
order by usecounts desc, CP.bucketid
This produces redundant results since the sys.dm_exec_query_stats has the stats for each of the statements inside the query_plan. I intentionally avoided this DMV for that reason. I looked at using string functions for this first, but you limit yourself an the processing of the information inside of the execution plan when you do that. I do admit, that doing a CAST to nvarchar(max) and then using LIKE is faster, but this isn't something that I need stellar performance from. If you look at that closer, you will see that you have multiple entries for the same CP.plan_handle.
There are multiple types of Implicit Conversions that exist in the ShowPlanXML. For example if you run the following two queries:
SELECT EmployeeID, NationalIDNumber, LoginID
FROM HumanResources.Employee
WHERE NationalIDNumber = 112457891
go
SELECT EmployeeID, NationalIDNumber, LoginID
FROM HumanResources.Employee
WHERE NationalIDNumber = '112457891'
go
The first does a column side conversion which causes a Index Scan decreasing performance. Notice the operation is index scan.
The second does a parameter side conversion that doesn't affect the ability to get an Index Seek.Now it is index seek.
Your code will return results for the second query, where mine won't. There are two steps to finding the bad implicit conversions in my code.
The first just splits any Convert node from the StmtSimple nodes of the plan that has an Implicit="1" attribute:
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
The second part validates that the Convert node has a child ColumnReference node that is in the current database and has a schema other than the sys schema (there are a lot of implicit conversions against the system objects):
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1
You can get implicit conversions that have no impact to performance.
from http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx
