Sql Server 2012 introduced a new extended event warning, which tells you when you get a plan with a conversion in it that causes scans instead of seeks so that you know when a plan is used that could have been better if the data types were correct. This is a massive performance issue and will essentially make a filter or join non-sargable causing a table or index scan rather than a seek. The issues of implicit conversions has been covered many times before so I won’t go over it here (see http://sqlperformance.com/2013/04/t-sql-queries/implicit-conversion-costs, https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/ or even http://lmgtfy.com/?q=performance+issues+with+implicit+conversions)
The new extended event is called “sqlserver.plan_affecting_convert” and fires every time there is a conversion in a plan that stops sql from doing a seek. There are a number of different plan_affecting_converts so check the ConvertIssue attribute for “Seek Plan” for this particular issue.
To demo this, I have created a table with a varchar datatype and a unique index on the column so a seek can be performed, however I use an nvarchar to search the column which means that every single value in the column needs to be converted from a varchar to a nvarchar before the comparison can take place, the plan looks like this and there is a warning to say that it isn’t very good:
Now if you ware writing some T-Sql and you see this then you can change it there and then but if the code is loose on the server, previously to this event you need to do things like search the plan cache for implicit conversions remembering, there is a good example of this here:
and
http://www.brentozar.com/blitz/implicit-conversion/
Now with this extended event you can sit and wait and get notified when it happens, this lets you be much more pro-active in finding performance issues on your Sql Server. To create the event, either use the wizard to create the session or do something like:
CREATE EVENT SESSION [plans_with_conversions] ON SERVER
ADD EVENT sqlserver.plan_affecting_convert(
ACTION(package0.callstack,sqlos.cpu_id,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack))
WITH (STARTUP_STATE=OFF)
You will then get alerts that look like:
From this you have the text that caused the issue and the plan handle so you can track down the query that is being so mean to your cpu (and probably i/o subsysten) and make it play nice!
Hapy Extended Eventing!
Hello Ed,
is there something like this available/possible in SQL 2008 R2?
Hi Warren,
I don’t know of anything in the profiler that can do it.
The best that I can think of is searching the plan cache for the text “Implicit” which will at least tell you which plans are doing conversions.
Ed
Ed, is there a good way to do this and only capture one implicit conversion per occurrence? I forced a very simple one by looking up a unicode value against a varchar column. I got 6 events captured for that – looks like 3 “cardinality estimate” and 3 “seek plan”. They happened in pairs, with the first two being “compile_time = true”, but that would still leave 4 more events caught for compile_time = false. I don’t see anything to differentiate the events outside of that and a little more info caught on the seq = 1 value.
Using the following as the most recent alter – largely what you were using, but with some other filters. I’d found SQL Prompt, SSMS Intellisense, and EF all generated a lot of excess implicit conversions while working their magic.
ALTER EVENT SESSION [Implicit_Conversions] ON SERVER
DROP EVENT sqlserver.plan_affecting_convert;
ALTER EVENT SESSION [Implicit_Conversions] ON SERVER
ADD EVENT sqlserver.plan_affecting_convert(
ACTION(package0.callstack,package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.plan_handle,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
WHERE ((((([sqlserver].[like_i_sql_unicode_string]([expression],N’Convert_Implicit%’))
AND ([sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[database_name],N’master’)))
AND ([sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[database_name],N’msdb’)))
AND NOT ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N’%SQL Prompt%’)))
AND NOT ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N’%sys.%’))));
GO