SQL Server Implicit Conversions Matter!

While not all implicit conversions are equal implicit conversions matter. For example, just this week I ran into a trivial code change that caused some severe regression in performance. One simple filter change caused a query to consume 227 million reads compared to 212 reads. What did the filter do? Well, it forced an implicit conversion.

In this blog post you will learn what is an implicit conversion, how to identify it, and also how do you find the implicit conversion leading to the highest amount of reads in the query store.

What is a SQL Server Implicit Conversion?

This is a great question. The answer is when SQL Server optimizer has to convert data in a column to match the type you are using in your query. For example, if you had a column that was an Integer and you decided to filter off of a string “varchar” variable. ORM’s like Entity Framework is notoriously known for this. You would end up forcing an implicit conversion. This will force the optimizer to read every value in the column and cast it so the column can then be filtered based off your variable or another column that doesn’t match the same data type.

Let’s look at an example. Yes, this is exactly similar to the example mentioned in the first paragraph that consumed 227 million reads. We are just going to simplify it down for you with a copy of the AdventureWorks modified database.

SalesOrderID column is an INT, not VARCHAR. We are going to filter with a VARCHAR data type using the LIKE operator as shown below.

SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2012].[Sales].[SalesOrderDetailEnlarged]
  WHERE SalesOrderID LIKE '43659'

This will produce the following execution plan and statistics which is not ideal at all. We will get a warning for the implicit conversion and also go parallel scanning the whole table due to this conversion.

How to fix Implicit Conversions in SQL Server

Now that we know how to identify an implicit conversion I bet you might be wondering how do you resolve them? They usually come in two flavors. First, the columns do not match data types on a join. Second, the parameter or literals data type does not match with the column. Today, we will focus on the second time in the code below.

The only thing we change in our code is the type of filter.

SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2012].[Sales].[SalesOrderDetailEnlarged]
  WHERE SalesOrderID = 43659

How do I get Implicit Conversions from the Query Store?

Now that you understand implicit conversions you might wonder how do I find them? Well, hopefully, you are on SQL Server 2016 or higher and can query the execution plans in query store. If not, check out Jonathan Kehayias example to find implicit conversions in the plan cache.

Disclaimer: Plan cache will only have execution plans that are still in memory. Query store will only have execution plans that are in the query store. Also, I added variables for the start date, end date, and top offenders. Performance milage will vary based on how your query store is configured and used. run the query below at your own risk!

DROP TABLE IF EXISTS #plans
DROP TABLE IF EXISTS #tmp1
DROP TABLE IF EXISTS #tmp2
DROP TABLE IF EXISTS #finalplan
GO

DECLARE @StartDate datetime = dateadd(d,-1, getdate()),
 @EndDate datetime = getdate(), 
 @TopStmts INT = 1000

SELECT TOP (@TopStmts)
	SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
   qsp.plan_id
INTO #tmp1
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id
WHERE qrs.first_execution_time >= @StartDate 
AND qrs.last_execution_time <= @EndDate
group by qsp.plan_id
ORDER BY est_logical_reads DESC 


SELECT cte.*, TRY_CAST (qsp.query_plan AS XML) AS xml_query_plan 
INTO #tmp2
FROM #tmp1 cte
JOIN sys.query_store_plan qsp ON cte.plan_id = qsp.plan_id


SELECT #tmp2.* 
INTO #plans
FROM #tmp2;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dbname SYSNAME 
SET @dbname = QUOTENAME(DB_NAME());

;WITH XMLNAMESPACES 
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT p.est_logical_reads, p.sum_executions, p.avg_logical_io_reads, p.plan_id
,   stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQLStmt, 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS SchemaName, 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS TableName, 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS ColumnName, 
 ic.DATA_TYPE AS ConvertFrom, 
 ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, 
   t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo, 
   t.value('(@Length)[1]', 'int') AS ConvertToLength,
   p.xml_query_plan
   INTO #finalplan
   FROM #plans p
CROSS APPLY xml_query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) 
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t) 
CROSS APPLY stmt.nodes('.//PlanAffectingConvert[@ConvertIssue="Cardinality Estimate"]') AS ce(pt) 
JOIN INFORMATION_SCHEMA.COLUMNS AS ic 
ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') 
   AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') 
   AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') 
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1


SELECT fp.est_logical_reads, fp.sum_executions, fp.avg_logical_io_reads, fp.plan_id, 
qsq.query_id, qsq.query_hash, fp.SQLStmt, fp.SchemaName, fp.TableName, 
fp.ColumnName, fp.ConvertFrom, fp.ConvertTo, fp.ConvertFromLength, fp.ConvertToLength, fp.xml_query_plan
FROM #finalplan fp
JOIN sys.query_store_plan qsp ON fp.plan_id = qsp.plan_id
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
ORDER BY fp.est_logical_reads DESC