Recently the application I'm working on encountered performance issue on a particular table. When I check the queries executing on this table, I noticed a warning about CONVERT_IMPLICIT on the predicate of the query. Apparently the data type of the column is varchar, but the query is passing in nvarchar, hence the warning.
Out of curiosity, I try to see how much difference would passing the correct data type improves query performance by checking the execution plan. I got the following result:
To my surprise, when I'm using the matching data type varchar, the query cost is double compare to using nvarchar, even though it seems like the second query is not using the most efficient execution plan.
So is the relative query cost to be trusted, or is there something I missed?
Here's the execution plan.
If even the actual execution plan is not reliable, what is a better way to compare the execution cost of 2 queries?