Get told about explicit & implicit conversions in Sql 2012+ using extended events

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-costshttps://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:

Query and Execution Plan with Seek Warning

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:

 http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx

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:

 

Convert Warning Extended Event

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!

Advertisements

3 comments

  1. Warren · February 13, 2015

    Hello Ed,

    is there something like this available/possible in SQL 2008 R2?

    • Ed Elliott · February 13, 2015

      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

  2. paschott · February 20, 2016

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s