THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

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.

Published Friday, January 08, 2010 1:54 PM by Jonathan Kehayias

Comments

 

Alexander Kuznetsov said:

Hi Jonathan,

I noticed that you are using READ UNCOMMITTED. Can you explain why not SNAPSHOT?

Cheers.

January 8, 2010 2:06 PM
 

WIDBA said:

Great stuff, I am going to run this for any major release and start getting some developers to fix their code.

Thanks!

January 8, 2010 2:54 PM
 

ALZDBA said:

The use of snapshot would only create overhead to the system.  Read uncommitted should use less resources and would suite perfect for this diagnosis.

btw This is the query I use.

It produces more results regarding implicit conversions.

---

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

January 8, 2010 3:22 PM
 

Jonathan Kehayias said:

ALZDBA,

Your code doesn't produce more results, it 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.

January 8, 2010 4:00 PM
 

ALZDBA said:

Jonathan, your findings are correct,

but when I ran both queries togheter, only my version produced rows :(

I'm not that much into xml queries, bug I prefer having a result rather than none at all.

I'll have a closer look later on.

Johan

January 8, 2010 4:38 PM
 

Jonathan Kehayias said:

Johan,

Your last comment jogged my memory.  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.  The second does a parameter side conversion that doesn't affect the ability to get an 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.

January 8, 2010 6:10 PM
 

Jonathan Kehayias said:

Alex,

I picked READ UNCOMMITTED because it seemed like the best fit to me when querying the DMV's.  I don't want to lock anything while querying them, uncommitted data won't matter for the minimal usage that this query has.  I am open to discuss why that is bad for this particular case.  I rarely change my ISOLATION LEVEL.

January 8, 2010 6:16 PM
 

AaronBertrand said:

For DMV queries, I'm with you.  Why force tempdb to deal with all that?  The worst that can happen in this case is either you get a result that is no longer an issue or you miss an issue initially.  For the ones that have gone away in the meantime, they're no longer issues, right?  And for the ones that will be persistent, they will show up next time.

January 8, 2010 6:34 PM
 

Alex K said:

Hi Jonathan,

I did not mean to imply that your choice of isolation level is less than optimal, I was just curios. I am sorry that my question sounded impolite. Thanks a lot!

January 10, 2010 12:08 PM
 

Jonathan Kehayias said:

Alex,

I didn't take your comment as being impolite.  I am open to discussing the merits for SNAPSHOT over READ UNCOMMITTED for this if you think it is better.  Transactional consistency in this case isn't a requirement for what I am after, but I definitely don't want to create a performance impact to the system while querying the DMV's for this kind of information.  I've played with a number of different methods to reduce the impact of querying the plan cache XML documents for a number of different purposes I have used them for.

January 11, 2010 12:22 AM
 

Henrik Staun Poulsen said:

Jonathan,

When I run this on my clients db, I get quite a few Tinyint to Int conversions. Do these matter? I mean, it must be really easy to convert between tinyint and int.

Similar I see some conversions between smalldatetime and datetime. How expensive are these?

I expect that conversions between nvarchar and varchar are very expensive, and I intend to fix these if possible.

TIA

Henrik Staun Poulsen

January 19, 2010 2:41 AM
 

Jonathan Kehayias said:

Henrik,

Off hand I don't know, I'd have to build a test to be certain, but you can easily test if it is problemattic by running the queries in question to see if preventing the implicit conversion results in a index seek instead of an index scan.  If you already are getting a seek, its not a problem.

January 20, 2010 2:54 PM
 

matski said:

Hi Jonathan and anyone prepared to comment for that matter - why would you see the need to set the transaction level at all. I would have though querying the system views wouldn't jeproadize blocking of any underlying resource. I may well be wrong and would certainly like to understand if that is the case.

February 1, 2010 2:36 AM
 

Glenn Berry said:

Nice query, Jonathan.

January 14, 2011 4:42 PM
 

kendra little said:

Still super-useful, a year later. :)

January 19, 2011 1:44 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement