Implicit conversion: when does it hurt performance a lot? I know that when data types are the same it is good and it hurts performance when they are not.
But sometimes it seems to have very little impact and basically a waste of time to fix it. Other times it will have a huge impact and performance gets a lot better.
Of course there are many scenarios, e.g. a insert select where the data type of the select is not the same, but compatible, with the insert column. Or it could be on the join predicates.
Where does implicit conversion hurt the most?
Also it seems that one also gets an implicit conversion when the lengths don't match, e.g. joining varchar(10) with varchar(20). How big a deal is these scenarios when we are only dealing with the length of the columns and not their type?
Basically I would like to know when I should worry about the compute scalar operator in the execution plan. How can I know if removing it will have a significant impact?
As Justin implies, the when it's a problem will vary from query to query and execution plan to execution plan. There's no definitive list of scenarios that guarantee you'll experience performance issues as a result of implicit conversion.
One way to tell if a query facing implicit conversion is experiencing performance issues is to check for cardinality estimate issues, generally the two go hand-in-hand when the implicit conversion is causing performance issues. You can do so by checking the Actual Number of Rows vs the Estimated Number of Rows for each operation of the query's execution plan, and if there's a large deviation between the two (generally an order of magnitude or more), then there's cardinality estimate issues. Generally you can look for the operations involved with the thickest lines to quickly spot which operations you can start checking cardinality estimate issues for. As Justin also mentioned, generally this is when the implicit conversion is happening on one of the indexed fields, specifically in a predicate (JOIN, WHERE, or HAVING clause) of the query.
But not all implicit conversion warnings are worth a grain of salt, and if you see such a warning but your query is running optimally, then you likely no need to spend time debugging it, especially if the execution plan doesn't also have cardinality estimate issues.