Implicit conversion… Sounds very clever, so it can’t be that bad, right? Well, in fact, implicit conversions can be huge performance killers and silent enough to stay off the radar, if we are not aware. The good news is that they tend to be very easy to solve, so let’s make an easy buck.
The story begins with mismatched data types on a JOIN operator or a WHERE clause that force SQL Server to implicitly convert values, on the fly. This can be as subtle as mistaking a VARCHAR for an NVARCHAR, although the consequences can be pretty awful. But, before diving into the problem itself, let’s talk about conversions and data type precedence.
Implicit vs Explicit Conversion
Data types can be either implicitly or explicitly converted.
Implicit conversions are nearly invisible to the user because they are automatically executed, i.e. they do not require the explicit use of any function.
Contrarily, in explicit conversions, CAST or CONVERT functions must be used. The recommendation is to use CAST function if ISO compliance is desirable and CONVERT function in order to take advantage of the style functionality.
Nonetheless, it’s not possible to convert every possible data type combination. The following matrix (provided by Microsoft here) shows all data type conversions for SQL Server system-supplied data types.
It’s important to notice that when an explicit conversion is done, the resulting data type is determined by the statement itself.
Implicit conversions like assignment statements, result in the data type that was defined by the variable declaration or column definition. For comparison operators or other expressions, the resulting data type depends on the rule of data type precedence.
Data Type Precedence
This rule simply states that, when an operator combines expressions of different data types, the data type with the lower precedence is converted to the data type with the higher precedence. The order is defined as follows:
|1. user-defined data types (highest)||11. real||21. text|
|2. sql_variant||12. decimal||22. image|
|3. XML||13. money||23. timestamp|
|4. datetimeoffset||14. smallmoney||24. uniqueidentifier|
|5. datetime2||15. bigint||25. nvarchar|
|6. datetime||16. int||26. nchar|
|7. smalldatetime||17. smallint||27. varchar|
|8. date||18. tinyint||28. char|
|9. time||19. bit||29. varbinary|
|10. float||20. ntext||30. binary (lowest)|
How to Uncover Implicit Conversions
There are a couple of ways to uncover implicit conversions. The most usual is, probably, by analyzing the Execution Plan. If we’re inspecting the execution of a query where an implicit conversion that may affect cardinality estimate happened, a warning will be displayed. We’ll further look into this.
It’s also possible to capture these occurrences through the use of DMVs. I’ve seen some examples using sys.dm_exec_query_plan DMV in order to accomplish it, but we’re going to use the brand new and awesome sys.dm_exec_query_plan_stats DMV brought by SQL Server 2019. We’ll take a deeper look into it in a future post, but for now just keep in mind that this DMV will show us the last actual plan for a given query. Also, make sure to activate the scoped configuration as specified below:
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON; --ImplicitConversions DECLARE @DBName VARCHAR(20) = 'FStats'; SELECT TOP 30 t.text AS 'Query', qs.total_worker_time/qs.execution_count AS 'Worker Time (Avg)', qs.total_elapsed_time/qs.execution_count AS 'Elapsed Time (Avg)', qs.total_logical_reads/qs.execution_count AS 'Logical Reads (Avg)', qs.total_physical_reads/qs.execution_count AS 'Physical Reads (Avg)', qs.max_elapsed_time AS 'Max Elapsed Time', qs.max_logical_reads AS 'Max Logical Reads', qs.max_physical_reads AS 'Max Physical Reads', qs.execution_count AS 'Execution Count', qp.query_plan AS 'Query Plan' FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) as qp WHERE t.text NOT LIKE '--ImplicitConversions%' AND CAST(query_plan AS NVARCHAR(MAX)) LIKE ('%PlanAffectingConvert%') AND t.[dbid] = DB_ID(@DBName)
Finally, another more comprehensive approach might be setting an Extedend Event to collect implicit conversions happening in the database. Creating a plan_affecting_convert event will allow us to uncover these CONVERT_IMPLICIT warnings. For the purposes of this demo it will be configured as it follows:
CREATE EVENT SESSION [xe_ImplicitConversions] ON SERVER ADD EVENT sqlserver.plan_affecting_convert ( ACTION(sqlserver.transaction_id) WHERE ([sqlserver].[database_name]=N'FStats') ) WITH (STARTUP_STATE=ON)
Implicit Conversions Woes
But why should we care about implicit conversions? Does it impact performance that much? Let’s find out! The following query retrieves the disciplinary sheet of football matches where 4 or more players were sent off.
DECLARE @NumRedCards tinyint = 4; SELECT MatchDetails, HF HomeFouls, AF AwayFouls, HY HomeYellowCards, AY AwayYellowCards, HR HomeRedCards, AR AwayRedCards, TRC TotalRedCards FROM MatchesDetails WHERE TRC >= @NumRedCards
Everything looks fine, right? Despite that, if you sense evil lurking, you’re absolutely correct. Using the previous xe_ImplicitConversions session, we’ll be able to see this:
Accordingly, the set of DMVs specified above, will reveal conversion problems that may affect cardinality estimate in the query plan choice.
If we click on the query plan and then hover on both operators, the following information will pop up:
Besides the obvious warnings, it’s clear that a nasty Clustered Index Scan was done and it’s quite strange that SQL Server didn’t cry for an index, isn’t it? Let’s create the bloody thing and ignore the implicit conversion, for now.
CREATE NONCLUSTERED INDEX IDX_MatchesDetails_TRC_Includes ON MatchesDetails(TRC) INCLUDE(MatchDetails,HF,AF,HY,AY,AR,HR) WITH(DATA_COMPRESSION=PAGE)
Noice! Now, with an unstoppable confidence, we will rerun the query and the plan will unequivocally show us that we’ve…
Failed. Miserably. An ugly Clustered Index Scan is still happening and the logical reads remain at 161 516.
Ok, so let’s change the approach here. First by dropping the index:
DROP INDEX IDX_MatchesDetails_TRC_Includes ON MatchesDetails
Then by analyzing the warning presented in the execution plan. According to it, there’s a data type mismatch between TRC and @NumRedCards. If we look into the definition of MatchesDetails table, we’ll confirm that TRC is, in fact, a varchar – genius.
Solving the Case
Elementary, my dear Watson.
Although the correct path here would be to alter the column data type, for simplicity’s sake we’ll just change the data type of @NumRedCards to varchar(2) and rerun the query.
DECLARE @NumRedCards varchar(2) = 4; SELECT MatchDetails, HF HomeFouls, AF AwayFouls, HY HomeYellowCards, AY AwayYellowCards, HR HomeRedCards, AR AwayRedCards, TRC TotalRedCards FROM MatchesDetails WHERE TRC >= @NumRedCards
This time, although the Clustered Index Scan hasn’t vanished, we got a missing index hint that corresponds exactly to the one created earlier. After recreating it, and rerunning the last query, the result will be the following:
Success! Just like a rainy cloud, the warning has disappeared and the day is sunny again. We’re now efficiently using the index with an Index Seek and the logical reads dropped to a glorious 4.
Cry Me a River
There’s nothing much left to say, in this context, regarding how implicit conversions can mess up the cardinality estimates and consequently ruin SARGability, index usage, and burn CPU. Despite that, it’s relevant to mention that implicit conversions aren’t always a problem, even if SQL Server says so. One example of that is when the conversion occurs on the SELECT statement, after the rows are fetched. SQL Server will have an embarrassing outbreak, threatening you with the same old warnings, stating that cardinality estimate can be affected, but it’s all a lie, it certainly won’t. Nevertheless, stay alert for the real performance killers! 🖖