Implicit
In a recent article, I covered a little bit about implicit conversions and an error that may crop up from time to time. It is one thing to understand what an error means, it is an entirely different problem to understand what is causing the error in the first place.
Seldom does a DBA have the opportunity to get out in front of infrequent or random errors such as implicit conversions. More often than not, it is the privilege of the DBA to find out about the problem after the fact from a developer or, worse yet, an end-user.
What if there was an easy method to track when these errors occurred? Even better, it would be ideal to understand what query caused the error as well as which types of queries are merely causing implicit conversion warnings. Even better, what if you had a way to understand which type of conversion problem is at hand.
Implicit Playground
To start this playground, let’s begin with a simple query based on the AdventureWorks database.
SELECT emp.BusinessEntityID , NationalIDNumber , LoginID FROM HumanResources.Employee emp WHERE NationalIDNumber = 112457891; GO
This query will yield a plan similar to the following.
If I hover the select operator, I will see a little window like the following.
I have a few items highlighted in this window. In the larger red box, I am outlining much of the type conversion warning. Inside of that, there is a blue box highlighting the “convert_implicit” warning. If I look further into the error message, there is text about query plan choice and “CardinalityEstimate” or “SeekPlan” for the different convert_implicit warnings. Another way of looking at it is as follows.
This seems a lot more friendly than that little context window. Here we have an easy to follow layout of what is happening. As I drill down into the warnings, I can see there are three convert_implicit warnings and they all trigger a “planaffectingconvert” type of warning. As we look closer at this, I can see there are two distinct types of convert issues.
There is one for the conversion from nvarchar to int in the HumanResources.Employee table for the NationalIDNumber field (green).
Two warnings that state that an index seek has been disabled due to the conversions ( orange and purple).
Curious to see what all of the possible “ConvertIssue” values could be for these implicit conversions? That is easy enough with the following query.
SELECT mv.name, mv.map_value, mv.map_key FROM sys.dm_xe_map_values mv WHERE mv.name = 'plan_affecting_convert_type';
Which shows us the following results.
That is a grand total of three. Having covered the cause of two of these already, all that is 
left is the type called “unknown”. I think we can safely presume that the cause of this one is something else that is undocumented and hence “unknown”.
So far, so good. We have seen how these warnings can crop up in execution plans. We have also seen how to query the for the “ConvertIssue” types or “plan_affecting_convert_type” from the Extended Events (XE) maps system DMV.
Right about now, the light bulb should be popping up for you. If we can query the XE metadata for the different convert issue types, then does that mean there is a way we can use XE to track these things and review them at our own convenience? Yes there is!
XE
Without further adieu, let’s look at an XE session that can be useful in discovering more information in regards to implicit conversions in the database environment.
USE master; GO -- Create the Event Session IF EXISTS ( SELECT* FROMsys.server_event_sessions WHERE name = 'ImplicitConvertError' ) DROP EVENT SESSION ImplicitConvertError ON SERVER; GO EXECUTE xp_create_subdir 'C:DatabaseXE'; GO CREATE EVENT SESSION [ImplicitConvertError] ON SERVER ADD EVENT sqlserver.error_reported ( ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) WHERE ( [error_number] = ( 257 ))) --fee free to uncomment these next few events if you wish to receive a ton more information --, ADD EVENT sqlserver.module_end --( ACTION ( ssqlserver.sql_text --, sqlserver.nt_username --, sqlserver.server_principal_name --, sqlserver.client_hostname --, package0.collect_system_time --, package0.event_sequence --, sqlserver.database_id --, sqlserver.database_name --, sqlserver.username --, sqlserver.session_nt_username --, sqlserver.client_app_name --, sqlserver.session_id --, sqlserver.context_info --, sqlserver.client_connection_id )) --, ADD EVENT sqlserver.rpc_starting --( ACTION ( sqlserver.sql_text --, sqlserver.nt_username --, sqlserver.server_principal_name --, sqlserver.client_hostname --, package0.collect_system_time --, package0.event_sequence --, sqlserver.database_id --, sqlserver.database_name --, sqlserver.username --, sqlserver.session_nt_username --, sqlserver.client_app_name --, sqlserver.session_id --, sqlserver.context_info --, sqlserver.client_connection_id )) --, ADD EVENT sqlserver.sql_statement_completed --( ACTION ( sqlserver.sql_text --, sqlserver.nt_username --, sqlserver.server_principal_name --, sqlserver.client_hostname --, package0.collect_system_time --, package0.event_sequence --, sqlserver.database_id --, sqlserver.database_name --, sqlserver.username --, sqlserver.session_nt_username --, sqlserver.client_app_name --, sqlserver.session_id --, sqlserver.context_info --, sqlserver.client_connection_id )) --, ADD EVENT sqlserver.sql_statement_starting --( ACTION ( sqlserver.sql_text --, sqlserver.nt_username --, sqlserver.server_principal_name --, sqlserver.client_hostname --, package0.collect_system_time --, package0.event_sequence --, sqlserver.database_id --, sqlserver.database_name --, sqlserver.username --, sqlserver.session_nt_username --, sqlserver.client_app_name --, sqlserver.session_id --, sqlserver.context_info --, sqlserver.client_connection_id )) , ADD EVENT sqlserver.plan_cache_cache_hit ( ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id )) , ADD EVENT sqlserver.plan_cache_cache_attempt ( ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id )) , ADD EVENT sqlserver.plan_affecting_convert ( ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) WHERE ( [package0].[equal_boolean]([sqlserver].[is_system], ( 0 )) AND [sqlserver].[database_id] > ( 4 ))) , ADD EVENT sqlserver.sp_cache_remove ( ACTION ( sqlserver.client_app_name , sqlserver.sql_text ) WHERE ( [sqlserver].[is_system] = ( 0 ))) , ADD EVENT sqlserver.uncached_sql_batch_statistics ( ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) WHERE ( [sqlserver].[is_system] = ( 0 ))) ADD TARGET package0.event_file (SET filename = N'C:DatabaseXEImplicitConvertError.xel', max_rollover_files = ( 5 )) WITH ( MAX_MEMORY = 4096KB , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS , MAX_DISPATCH_LATENCY = 3 SECONDS , MAX_EVENT_SIZE = 0KB , MEMORY_PARTITION_MODE = NONE , TRACK_CAUSALITY = ON , STARTUP_STATE = OFF ); GO
This session is pretty comprehensive with the ability to be very much like a fire-hose session if the commented out events are uncommented prior to session creation. Running this on a busy server ( less than 1 minute) for a client with a third party vendor application produces results that look like the following.
As we can see here, the session is trapping when I have any of those pesky implicit conversion errors (red box outline) as mentioned in the previous article, as well as the convert_implicit warnings (purple box outline) that were demonstrated in the execution plans in this article.
How am I trapping the implicit conversion error? That is done via the following piece of the event session shown above.
ADD EVENT sqlserver.error_reported ( ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) WHERE ( [error_number] = ( 257 ))) --implicit conversion error
Error number 257 is the message id for the implicit conversion error. When filtering on that ID for this event, then I will receive only the implicit errors that match that number.
As for the implicit conversion warnings, we have the following that accomplishes that requirement.
, ADD EVENT sqlserver.plan_affecting_convert ( ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) WHERE ( [package0].[equal_boolean]([sqlserver].[is_system], ( 0 )) AND [sqlserver].[database_id] > ( 4 )))
The event, plan_affecting_convert should seem eerily familiar at this point. We have seen terminology like that in the execution plans as well as in the query for the map values.
The combination of these events will provide great insight into the issues revolving around any implicit conversions you may be having in your database environment.
Wrapping it Up
Implicit conversions are a fail-safe for when bad design or code (or both) crops up in your environment. When this fail-safe is being used in your environment, it is helpful to gain further insights via Extended Events. This article demonstrated another use for Extended Events. If you are in need of a little tune-up for your XE skiils, I recommend reading a bit on Extended Events to get up to date. For other basics related articles, feel free to read here.