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.
|
1 2 3 4 5 6 7 |
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.
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 |
USE master; GO -- Create the Event Session IF EXISTS ( SELECT * FROM sys.server_event_sessions WHERE name = 'ImplicitConvertError' ) DROP EVENT SESSION ImplicitConvertError ON SERVER; GO EXECUTE xp_create_subdir 'C:\Database\XE'; 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:\Database\XE\ImplicitConvertError.xel', max_rollover_files = ( 5 )) WITH ( MAX_MEMORY = 4096KB , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS , MAX_DISPATCH_LATENCY = 3 |