When do conversions generate conversion warnings? And why are these bad?
Hello,
A few days ago a colleague asked me why was he seeing implicit conversions in the execution plan, but no warnings of such sort were being issued. Warnings are seen in the plan if the conditions they affect occur, and then are surfaced in the <Warnings> element of SHOWPLAN, either as affecting Cardinality Estimates and/or affecting the ability to Seek on an index.
Conversion related warnings are not exclusive to implicit converts. Any convert has the potential to spawn such warning. Warnings in the execution plan come from heuristics, and in the case of conversions, heuristics are set to fire the a conversion warning any time an existing convert *might* not preserve the order and thus affecting estimates, which in turn might affect the ability to seek, whether it is used in a predicate or not – although these will be more commonly seen in a search or join predicate.
Now, when referring to “order affecting conversions”, it becomes clearer that there is a connection between this concept, and conversions from string to numeric data types, or unicode to non-unicode strings, and why these would show up in SHOWPLAN as warnings. Let me to illustrate all of the above with a few examples.
As we can see in the plan below, executing the next query generates a warning, because there is a string to numeric conversion (explicit) in the s.SalesOrderNumber computed column. This column definition is (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***')), so it uses one of the columns in the join predicate. There are some implicit conversions going on in the plan, namely on d.LineTotal column, but these are not generating warnings, as they are not affecting estimations.
USE [AdventureWorks2012]
GO
SELECT s.SalesOrderID, s.OrderDate, s.DueDate, s.ShipDate, s.Status, s.SalesOrderNumber, s.AccountNumber, d.LineTotal
FROM Sales.SalesOrderHeader AS s
INNER JOIN Sales.SalesOrderDetail AS d ON s.SalesOrderID = d.SalesOrderID
WHERE (d.OrderQty = 3.0)
GO
By removing the computed column s.SalesOrderNumber we avoid generating a warning as seen in the plan below, because the join predicate is not being converted to a string now, so it does not affect possible order and does not interfere with estimates.
SELECT s.SalesOrderID, s.OrderDate, s.DueDate, s.ShipDate, s.Status, s.AccountNumber, d.LineTotal
FROM Sales.SalesOrderHeader AS s
INNER JOIN Sales.SalesOrderDetail AS d ON s.SalesOrderID = d.SalesOrderID
WHERE (d.OrderQty = 3.0)
GO
Now, in the next example, given to me to illustrate my colleague's question of why an implicit conversion was not generating a warning, we can see that indeed no warning exists, because in the filter operator (plan below) there is no conversion going on. Note that we are still doing some implicit conversions on LineTotal column, but like before, these are not generating warnings.
SELECT * FROM [Sales].[SalesOrderDetail]
WHERE [OrderQty] = 3.0
GO
What if we were to add the following computed column to the table and use it in the query as a predicate?
ALTER TABLE [Sales].[SalesOrderDetail]
ADD [OrderQtyStr] AS (CONVERT([nvarchar](10),[OrderQty]))
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE [OrderQtyStr] = 3
GO
We can see how the predicate changed, and is now using a scalar expression (conversion):
And how a warning is generated (actually more than that) as seen below:
- One for calculating the computed column used in the predicate (blue);
- One for converting the computed column back to int for comparison with the predicate (Expr1004 in green)
- And a couple stating the conversions also disallow the use of indexes to seek (seekplan warnings in red)
Now for a last example:
SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber = 671089628
GO
The predicate is a unicode string to numeric conversion – NationalIDNumber is an NVARCHAR(15) and the predicate is an integer – to see the rules of precedence when doing conversions, please refer to the BOL article on Data Type Precedence.
Therefore we have the cardinality estimation warning because the conversion affects ordering (blue), and in this case also a couple seekplan warnings because this conversion also affects the ability to seek - if we change the ID number to string, then we indeed would use a seek.
Let me wrap up with my general rule of thumb: generating a warning or not, implicit conversions are a sign of added cpu cycles and probably a poor design choice, either on the database level or just with coding (like poorly declared variables), so as a best practice I aim to remove these implicit conversions when I find them anyway – more so if they are generating warnings of course.
Hope this is useful. Until next time!
Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.