Performance Woes & Wows #1: Implicit Conversions

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. real21. text
2. sql_variant12. decimal22. image
3. XML13. money23. timestamp
4. datetimeoffset14. smallmoney24. uniqueidentifier
5. datetime215. bigint25. nvarchar
6. datetime16. int26. nchar
7. smalldatetime17. smallint27. varchar
8. date18. tinyint28. char
9. time19. bit29. varbinary
10. float20. ntext30. binary (lowest)
Data type precedence (Transact-SQL)

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! 🖖

MemoryGrantInfo Info

As suggested by the title, I like redundancy, bad titles and we’re going to have a brief look into MemoryGrantInfo.

In a previous post, we’ve talked about an awesome feature from the new SQL Server 2019 – Row Mode Adaptive Memory Grant Feedback. We saw this feature in action and briefly explored MemoryGrantInfo, but some fields were not explained. So, let’s recall the MemoryGrantInfo section and analyze its properties:

DesiredMemory: Amount of memory (in kilobytes) estimated to be needed to run the execution plan, based on the cardinality and data size estimation. This value will be cached as part of the plan and will remain the same – if Adaptive Memory Grant Feedback is not enabled.

GrantedMemory: Amount of memory that was actually granted at runtime. This value can be lower than DesiredMemory if the server doesn’t have enough available memory at the moment of the grant. Due to this fact, it’s possible that its value differs across similar executions, depending on the server’s available workspace memory at the time. When the grant isn’t high enough to keep all data in memory it will write it into tempdb – this is also known as a spill and it can degrade query performance. This doesn’t mean that a lower grant will always cause a spill, nor that a spill will always have a substantial impact on performance, nevertheless, that’s definitely something to keep an eye on.

GrantWaitTime: Time (in seconds) that the query had to wait before it was granted the memory to execute. This will translate into RESOURCE_SEMAPHORE waits.

IsMemoryGrantFeedbackAdjusted: Current state of Adaptive Memory Grant Feedback, that will be represented by one of the following values: NoFirstExecution, YesAdjusting, YesStable, NoAccurateGrant, NoFeedbackDisabled. For more details, check this post about Adaptive Memory Grant Feedback.

LastRequestedMemory: Indicates RequestedMemory value from the previous execution. While trying to calculate the ideal memory grant, Adaptive Memory Grant Feedback will use the requested value from an execution in order to improve the following. This property will allow us to easily check how the grant is being adapted.

MaxQueryMemory: Maximum memory allowed for a single query, according to Resource Governor’s MAX_MEMORY_PERCENT configuration, which by default is 25% of total query memory. In a case where operators are spilling data into tempdb and estimates are fairly correct, we might be experiencing memory starvation.

MaxUsedMemory: Amount of memory actually used by the query during execution.

RequestedMemory: Amount of memory requested, based on RequiredMemory, DesiredMemory, SerialRequiredMemory, SerialDesiredMemory, and MaxQueryMemory. When any query is executed, SQL Server Engine checks if the desired memory exceeds the Max Query Memory or not, if it exceeds, then it reduces the requested memory by adjusting parameters as Degree Of Parallelism (DOP), until it fits in.

RequiredMemory: This property represents the required memory to create the respective internal data structures, for a given DOP, when the query runs in parallel. If the query is running in serial mode, RequiredMemory value will be the same as SerialRequiredMemory. It’s mandatory that this amount of memory is available, in order to the query to begin execution.

SerialDesiredMemory: Amount of memory estimated to be needed to run the execution plan on serial mode, without spilling to disk.

SerialRequiredMemory: Required memory for a serial query plan to execute. It’s mandatory that this amount of memory is available in order to the query to begin execution.

And that’s it.